Five TSQL scripts to partition a table by date to split data on 3 partitions like Cold, Warm, and Hot categories.
Step 1 : Create 3 news partitions on database
----------------------------------------------------------------------------------------------------------------------- -- Use tab stop value = 2 et indent spaces = 2 ----------------------------------------------------------------------------------------------------------------------- -- Name : database_partition_cold_warm_hot -- Author : Thierry Bertin -- : www.abcdata.ca -- Date : 2018-04-09 -- Comment : Replace [default file location] and [database name] by the right names ----------------------------------------------------------------------------------------------------------------------- -- Change Log : Date - Author - Description -- ----------------------------------------------------------------------------------------------------- -- 2018-04-09 - Thierry Bertin - Create a 3 partitions for Cold, Warm, Hot Data ----------------------------------------------------------------------------------------------------------------------- alter database [database name] add filegroup [Part_Cold_Data]; go alter database [database name] add file ( name = [Part_Cold_Data] , filename = '[default file location]\[database name]_Part_Cold_Data.ndf' , size = 500MB , maxsize = 'unlimited' , filegrowth = 500MB ) to filegroup [Part_Cold_Data]; go alter database [database name] add filegroup [Part_Warm_Data]; go alter database [database name] add file ( name = [Part_Warm_Data] , filename = '[default file location]\[database name]_Part_Part_Warm_Data.ndf' , size = 500MB , maxsize = 'unlimited' , filegrowth = 500MB ) to filegroup [Part_Warm_Data]; go alter database [database name] add filegroup [Part_Hot_Data]; go alter database [database name] add file ( name = [Part_Hot_Data] , filename = '[default file location]\[database name]_Part_Part_Hot_Data.ndf' , size = 500MB , maxsize = 'unlimited' , filegrowth = 500MB ) to filegroup [Part_Hot_Data]; go -----------------------------------------------------------------------------------------------------------------------
Step 2 : Create a partition function on database
----------------------------------------------------------------------------------------------------------------------- -- Use tab stop value = 2 et indent spaces = 2 ----------------------------------------------------------------------------------------------------------------------- -- Name : database_partition_function_cold_warm_hot -- Author : Thierry Bertin -- : www.abcdata.ca -- Date : 2018-04-09 -- Comment : Parition function is related to paritions schemes ----------------------------------------------------------------------------------------------------------------------- -- Change Log : Date - Author - Description -- ----------------------------------------------------------------------------------------------------- -- 2018-04-09 - Thierry Bertin - Create partition function to partitionning tables on datetime column ----------------------------------------------------------------------------------------------------------------------- drop partition function Cold_Warm_Hot_PF; create partition function Cold_Warm_Hot_PF (datetime) as range right for values ( -- Cold Data '2016-12-31' -- Warm Data , '2017-12-31' -- Hot Data , '2018-01-01' ); go -----------------------------------------------------------------------------------------------------------------------
Step 3 : Create a partition scheme on database
----------------------------------------------------------------------------------------------------------------------- -- Use tab stop value = 2 et indent spaces = 2 ----------------------------------------------------------------------------------------------------------------------- -- Name : database_partition_scheme_cold_warm_hot -- Author : Thierry Bertin -- : www.abcdata.ca -- Date : 2018-04-09 -- Comment : Parition names are related to paritions created on script that create the partitions ----------------------------------------------------------------------------------------------------------------------- -- Change Log : Date - Author - Description -- ----------------------------------------------------------------------------------------------------- -- 2018-04-09 - Thierry Bertin - Create a partition scheme to partitionning tables ----------------------------------------------------------------------------------------------------------------------- create partition scheme Cold_Warm_Hot_PS as partition Cold_Warm_Hot_PF to ( -- Cold Data Part_Cold_Data -- Warm Data , Part_Warm_Data -- Hot Data , Part_Hot_Data ); go -----------------------------------------------------------------------------------------------------------------------
Step 4 : Create partition index on the table
----------------------------------------------------------------------------------------------------------------------- -- Use tab stop value = 2 et indent spaces = 2 ----------------------------------------------------------------------------------------------------------------------- -- Name : Create_Index_Clustered -- Author : Thierry Bertin -- : www.abcdata.ca -- Date : 2018-04-09 -- Comment : To add data table to partitions ----------------------------------------------------------------------------------------------------------------------- -- Change Log : Date - Author - Description -- ----------------------------------------------------------------------------------------------------- -- 2018-04-09 - Thierry Bertin - Create clustered index on table to partition this table ----------------------------------------------------------------------------------------------------------------------- -- Remove existing clustered index alter table dbo.[your table name to be partitionned] drop constraint [your table name to be partitionned]_PK with (online = off); go -- Add the same index nonclustered alter table dbo.[your table name to be partitionned] add constraint [your table name to be partitionned]_PK primary key nonclustered ([your table name to be partitionned]_id asc) -- Column that is used as primary key with (pad_index = off , statistics_norecompute = off , ignore_dup_key = off , allow_row_locks = on , allow_page_locks = on) on [large_table_index_01]; -- default partition for indexes on large table to not have indexes on Primary partition go -- Add new clustered index for partitionning create clustered index [your table name to be partitionned]_Part on [your table name to be partitionned] (date) -- Partitionning by date column on table [your table name to be partitionned] with (pad_index = off , statistics_norecompute = off , ignore_dup_key = off , allow_row_locks = on , allow_page_locks = on) on Cold_Warm_Hot_PS (date); go -----------------------------------------------------------------------------------------------------------------------
Step 5 : Validate that data is split on partitions
----------------------------------------------------------------------------------------------------------------------- -- Use tab stop value = 2 et indent spaces = 2 ----------------------------------------------------------------------------------------------------------------------- -- Name : Partition_Validate -- Author : Thierry Bertin -- : www.abcdata.ca -- Date : 2018-04-09 -- Comment : To validate that data is correctly spread into the partitions ----------------------------------------------------------------------------------------------------------------------- -- Change Log : Date - Author - Description -- ----------------------------------------------------------------------------------------------------- -- 2018-04-09 - Thierry Bertin - To validate the partitionning ----------------------------------------------------------------------------------------------------------------------- declare @Table_Name varchar (200); set @Table_Name = 'your partitionned table name'; select distinct o.name as table_name , i.name as index_name , p.partition_number , fg.name as filegroup_name , p.rows as 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 i.index_id = p.index_id inner join sys.allocation_units au on au.container_id = p.hobt_id inner join sys.filegroups fg on fg.data_space_id = au.data_space_id where o.name = @Table_Name; -----------------------------------------------------------------------------------------------------------------------