SQL Server Partition table
This is a simple tutorial for creating partitions
on an existing table in Microsoft SQL Server.
▋Microsoft SQL Server 2017 Express
▋Create a demo table
Lets create a table first. We will use [CreateOn] as the the value for partitioning.
CREATE TABLE [dbo].[OnlineTxs]
(
[Id] [numeric](18, 0) IDENTITY (1,1) NOT NULL,
[CardNo] [varchar](19) NOT NULL,
[Amt] [decimal](10, 0) NOT NULL,
[CreateOn] [datetime] NOT NULL,
CONSTRAINT [PK_OnlineTxs] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
▋ Create
Partition Function and Scheme
Use Date as the partition boundary of the Partition Function.
CREATE PARTITION FUNCTION pf_date_range (datetime)
AS RANGE RIGHT
FOR VALUES ('2020/11/23','2020/11/24')
-- FOR VALUES (CONVERT(VARCHAR(10), dateadd(day,-1, GETDATE()), 111)) -- Yesterday
GO
CREATE PARTITION SCHEME ps_date_range
AS PARTITION pf_date_range ALL TO ([PRIMARY])
GO
Verify the partitions we just made by
the following SQL.
SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
The records will be stored in this way,
|
Partition number
|
Range
|
|
1
|
< 2020-11-23 00:00:00
|
|
2
|
>= 2020-11-23 00:00:00 & <
2020-11-24 00:00:00
|
|
3
|
>= 2020-11-24 00:00:00
|
|
|
|
▋ Create the none-clustered constraint and create clustered
index
Furthermore, we have to recreate the
constraint, the primary key must
• Includes the Partition Boundary column:
[CreateOn]
• None-clustered
• Be added on the Partition Function
And create a clustered index for the
Partition Boundary column: [CreateOn].
ALTER TABLE [dbo].[OnlineTxs] DROP CONSTRAINT [PK_OnlineTxs]
GO
ALTER TABLE [dbo].[OnlineTxs]
ADD CONSTRAINT [PK_OnlineTxs] PRIMARY KEY NONCLUSTERED ([Id] ASC, [CreateOn])
ON ps_date_range([CreateOn]);
GO
-- Create cluster index
CREATE CLUSTERED INDEX [IX_OnlineTxs_createon] ON [dbo].[OnlineTxs] ([CreateOn])
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON ps_date_range([CreateOn])
GO
▋Test the partition table with data
We can use the following SQL to
generate some data into the table.
CREATE FUNCTION fn_get_random (@GUID uniqueidentifier, @CEILING_VALUE INT)
RETURNS INT
AS
BEGIN
DECLARE @RAND INT;
SELECT @RAND = ABS(CHECKSUM(@GUID)) % @CEILING_VALUE;
RETURN @RAND;
END
-- Create mock data
DECLARE @seq int = 0
WHILE @seq < 1000
BEGIN
SET @seq = @seq + 1
INSERT INTO [dbo].[OnlineTxs]([Cardno],[Amt],[CreateOn])
SELECT '123456****789' AS [CardNo], dbo.fn_get_random(NEWID(), 9999) AS [Amt], DATEADD(day, -1 * dbo.fn_get_random(NEWID(),4), GETDATE()) AS [CreateOn]
END
Check the data by summarizing by Partition Number,
SELECT o.name AS [ObjectName],i.name AS [IndexName], partition_id AS [PartitionId], partition_number AS [PartitionNumber], [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 '%OnlineTxs%'
ORDER BY [IndexName], [PartitionNumber]
Or list/count the records by Partition Number,
DECLARE @partition_number INT = 1;
SELECT COUNT(*) AS Cnt FROM [dbo].[OnlineTxs]
WHERE $PARTITION.pf_date_range([CreateOn]) = @partition_number;
▋Move on more actions
▋How to truncate by
Partition
Truncating by Partition is much faster
than doing deletion. Here is how to truncate rows in millionseconds by
Partition Number.
DECLARE @partition_numer INT = 1;
TRUNCATE TABLE [dbo].[OnlineTxs] WITH (PARTITIONS(@partition_number));
▋How to create new Partition
Alter the Partition Function to have
more Partitions,
ALTER PARTITION SCHEME ps_date_range NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_date_range() SPLIT RANGE ('2020/11/25');
▋How to remove the Partition
ALTER PARTITION FUNCTION pf_date_range() MERGE RANGE(CONVERT(DATETIME, '2020-11-23'));
Notice that the parameter for RANGE() must be DATETIME.
▋Remove Partition Scheme and Function
DROP PARTITION SCHEME ps_date_range;
DROP PARTITION FUNCTION pf_date_range;
▋How to Partition an
existing SQL Server Table
