This post explains how to implement table partitioning for an existing table in SQL Server.
Let us create a table as below for our explanation.
Create Table and Populate table data
Create Table AuditData (ID int identity(1,1) Primary Key not null, AuditDate Datetime not null)
Insert into AuditData Values(getdate() - (365*5)) ,(getdate() - (365*4)) ,(getdate() - (365*3))
,(getdate() - (365*2)) ,(getdate() - (365*1)) ,(getdate()) ,(getdate() + (365*5))
,(getdate() + (365*4)) ,(getdate() + (365*3)) ,(getdate() + (365*2)) ,(getdate() + (365*1))
--Check the data
Select * From AuditData
The above code will create a table called AuditData and inserts a single row for every previous and upcoming 5 years from now. Please note this test data is created on year 2020, so there might be difference in the data at the time you refer this blog.
Check the partition and its data allocation
select Object_name(p.object_id) Table_Name ,(Select name From sys.indexes where Object_id = p.object_id and index_id = p.index_id) 'Index_name',
partition_number, lv.value leftValue, rv.value rightValue,p.rows,
s.name 'Partition_Schema_Name',f.name 'Partition_Function_Name'
from sys.partitions p
join sys.allocation_units a on p.hobt_id = a.container_id
join sys.indexes i on p.object_id = i.object_id
Left join sys.partition_schemes s on i.data_space_id = s.data_space_id
left join sys.partition_functions f on s.function_id = f.function_id
left join sys.partition_range_values rv on f.function_id = rv.function_id and p.partition_number = rv.boundary_id
left join sys.partition_range_values lv on f.function_id = lv.function_id and p.partition_number - 1 = lv.boundary_id
where p.object_id = object_id('AuditData')
From the above, its clear that the index created on the table as part of Primary key has only one partition and all the data is part of that partition. Now, let us quickly convert this existing table as as partitioned table. To do so, we need to create partition function and partition schema as below. The function fn_AuditDate has been created on Datetime field so that in our example, we can create partition based on AuditDate.
--Create partition function on Datetime
Create partition function fn_AuditDate (Datetime) as
Range right for values('20150101', '20160101','20170101','20180101', '20190101','20200101')
/*Please note this test data is created on year 2020, so there might be difference in the data at the time you refer this blog.*/
--Create partition Schema to associate the function
Create partition scheme sc_AuditDate As
Partition fn_AuditDate ALL to ([Primary])
Partition scheme ‘sc_AuditDate’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup in partition scheme ‘sc_AuditDate’.
Recreate clustered index to make the partition column as part of clustered index
ALTER TABLE dbo.AuditData DROP CONSTRAINT [PK__AuditDat__3214EC2760DC1A18]
GO
ALTER TABLE dbo.AuditData ADD CONSTRAINT [PK__AuditDat__3214EC2760DC1A18] PRIMARY KEY NONCLUSTERED (ID)
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_AuditData_AuditDate_ID ON dbo.AuditData (AuditDate)
ON sc_AuditDate(AuditDate)
GO
Now, it is important to make sure that the clustered index to be recreated on partition schema to partition the AuditData table. If you look at the above example, since the clustered index is created as part of Primary key creation, we need to drop the primary key and re create the primary key as non clustered index and create a separate clustered index on AuditDate. Note that the clustered index is created on partition that we created recently – sc_AuditDate.
Check the partition and its data allocation
select Object_name(p.object_id) 'Table_Name',(Select name From sys.indexes where Object_id = p.object_id and index_id = p.index_id) 'Index_name',
partition_number, lv.value leftValue, rv.value rightValue,p.rows,
s.name 'Partition_Schema_Name',f.name 'Partition_Function_Name'
from sys.partitions p
join sys.allocation_units a on p.hobt_id = a.container_id
join sys.indexes i on p.object_id = i.object_id
join sys.partition_schemes s on i.data_space_id = s.data_space_id
join sys.partition_functions f on s.function_id = f.function_id
left join sys.partition_range_values rv on f.function_id = rv.function_id and p.partition_number = rv.boundary_id
left join sys.partition_range_values lv on f.function_id = lv.function_id and p.partition_number - 1 = lv.boundary_id
where p.object_id = object_id('AuditData')
Now, you can see the clustered index has 7 partitions as per the range that we defined in the schema and function. The last partition contains all the data right to the range (eg: 2020/2021/2022/2023/2024/2025); 6 rows in our AuditData table.
Hope this explains how to partition an existing table simply. But wait, that may not be so easy as I explained for your real time scenario. You may want to implement partition on a table that has lots of data/ multiple indexes/foreign key relations defined etc. We need to carefully evaluate the steps to reduce the down time during the implementation of partition. I would suggest partitioning a table (especially bigger ones) should be an offline activity rather than an online for better performance and easy implementation and testing.
Cleanup Objects
Clean up is very important, so my test objects.
DROP TABLE AuditData
DROP PARTITION SCHEME sc_AuditDate
DROP PARTITION FUNCTION fn_AuditDate
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
SQLZealots 
One thought on “Stairway to SQL Server Table Partitioning – How do we partition an existing table”