database table partition

Some database tables grow and become a super large tables. Usually we either archive or partition the large tables. There are pros and cons to everything.

Here I am showing the partition details:

There are 2 different ways to partition the table.

One way is to create a brand new partitioned table and then copy the data from your existing table into the new table and do a table rename. This is not practical for the existing database since the table maybe too large.

Another way is to partition the existing table in place by rebuilding or creating a clustered index on the table, this is commonly used to improve the performance on the existing program/database, here is the details:

1. Generate a sample table:

CREATE TABLE [dbo].[tblPartition](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](50) NOT NULL,

[dtCreated] [datetime] NOT NULL,

CONSTRAINT [PK_Partition] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblPartition] ADD CONSTRAINT [DF_Partition_dtCreated] DEFAULT (getdate()) FOR [dtCreated]

GO

2. load testing data:

DECLARE @val INT

SELECT @val=1

WHILE @val < 1000

BEGIN

INSERT INTO dbo.tblPartition([name],[dtCreated])

VALUES ('Daniel TEST' + CONVERT(varchar(10), @val),getdate()-1500 +@val)

SELECT @val=@val+1

END

go

3. Search the sys.partitions system view we can see how only 1 partition on the target table(i.e. tblPartition).

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]

FROM sys.partitions p

INNER JOIN sys.objects o ON o.object_id=p.object_id

INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id

WHERE o.name LIKE '%tblPartition%'

4. In order to create a partitioned table we'll need to first create a partition function and partition scheme. For our example we are going to partition the table based on the datetime column. Here is the code to create these objects and check some of their metadata in the system views.

CREATE PARTITION FUNCTION PartitionFunctionDT (datetime)

AS RANGE RIGHT FOR VALUES ('20240301', '20240601','20240901','20250101')

GO

CREATE PARTITION SCHEME PartitionSchemeDT

AS PARTITION PartitionFunctionDT ALL TO ([PRIMARY])

GO

We can view the Partition Functions and Schemes by running following query to verify:

SELECT * FROM sys.partition_schemes;

SELECT * FROM sys.partition_functions;

go

5. we are going to partition the table using a clustered index. if the table already has a clustered index defined we'll need to drop this index first and recreate the constraint using a non-clustered index.

ALTER TABLE dbo.tblPartition DROP CONSTRAINT PK_Partition

GO

ALTER TABLE dbo.tblPartition ADD CONSTRAINT PK_Partition PRIMARY KEY NONCLUSTERED (ID)

WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.tblPartition (dtCreated)

WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON PartitionSchemeDT(dtCreated)

GO

6. Now we can verify 5 partitions created on this table with following query:

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]

FROM sys.partitions p

INNER JOIN sys.objects o ON o.object_id=p.object_id

INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id

WHERE o.name LIKE '%tblPartition%'

GO

We had partitioned a table now. We may need to re-partition the table if the table grows quickly. I can share how to re-partition the existing table later.