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:
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).
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:
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.