TSQL – How to partition a table

database_partition_package

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;
-----------------------------------------------------------------------------------------------------------------------

Lien Permanent pour cet article : http://www.abcdata.ca/abc/?p=96