In this blog post, I will demonstrate how to partition your tables. I have created a sample database called deptstore.
There are 4 general steps in partitioning tables in SQL Server.
You can create two types of partition: RANGE partition and LIST partition.
In this post, I will only enumerate the procedures in creating RANGE partition but the process is essentially the same for creating LIST partition.
1. Create FileGroups for each of your partition.
USE [master] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q12008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q12008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q12008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q12008] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q22008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q22008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q22008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q22008] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q32008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q32008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q32008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q32008] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q42008] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q42008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q42008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q42008] GO
2. Create a Partition Function. This defines how your table is going to be partitioned. You have to specify the data type that your column is dealing with. Also you have to specify the partition/dividing points.
CREATE PARTITION FUNCTION date_part_func (DATETIME) AS RANGE RIGHT FOR VALUES('20080401','20080701','20081001')
Note: The word RIGHT means that if data we are dealing with is exactly 01 July, then it will go to the right partition. In other words it will go to the partition that is in the 3rd Quarter.
If we say LEFT values, then the records will go to quarter 2.
The screenshot below shows you where you can find the create partition function in SSMS.
3. Create a Partition Scheme. This will bind particular paritions with the appropriate FileGroups.
CREATE PARTITION SCHEME date_part_scheme AS PARTITION date_part_func TO (q1,q2,q3,q4)
Below is where you can find the created partition scheme in SSMS
4. Create a table that is going to use that Partition Scheme. Ensure that the table you are creating has a column with the data type that you mentioned above (DATETIME).
CREATE TABLE invoices ( id INT NOT NULL ,inv_date DATETIME ,balance NUMERIC ) ON date_part_scheme(inv_date) --specify partitioning column
To test, insert records to the table
INSERT INTO invoices VALUES(1,'20081204',5000) INSERT INTO invoices VALUES(2,'20080202',3000) INSERT INTO invoices VALUES(3,'20080803',5000)
Check which records went in which partition.
SELECT $PARTITION.date_part_func(inv_date) partition, [id], inv_date, balance FROM invoices;
Note: If we insert data that is not in year 2008? That data will just go to the last partition. It is considered best practice to have your partitions on the left side and right hand (both sides) to be like a catch all partition.
Check partition info
SELECT pf.name AS pf_name , ps.name AS partition_scheme_name , p.partition_number , ds.name AS partition_filegroup , OBJECT_NAME(si.object_id) AS object_name , rv.value AS range_value , SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows ELSE 0 END) AS num_rows FROM sys.destination_data_spaces AS dds JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id AND dds.destination_id = CASE pf.boundary_value_on_right WHEN 0 THEN rv.boundary_id ELSE rv.boundary_id + 1 END LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id AND si.index_id = p.index_id AND dds.destination_id = p.partition_number LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id AND p.partition_id = dbps.partition_id GROUP BY ds.name , p.partition_number , pf.name , pf.type_desc , pf.fanout , pf.boundary_value_on_right , ps.name , si.object_id , rv.value ORDER BY p.partition_number GO
SPLITTING PARTITIONS
Before we split any partitions, we need to actually create new FileGroups to hold the new partition.
USE [master] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q12009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q12009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q12009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q12009] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q22009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q22009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q22009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q22009] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q32009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q32009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q32009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q32009] GO ALTER DATABASE [deptstore] ADD FILEGROUP [q42009] GO ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q42009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q42009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q42009] GO
Alter the partition scheme. Tell it which partition to use next. And then you are going to alter the Partition Function to specify the new split value.
ALTER PARTITION SCHEME date_part_scheme NEXT USED q12009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20090101') ALTER PARTITION SCHEME date_part_scheme NEXT USED q22009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20090401') ALTER PARTITION SCHEME date_part_scheme NEXT USED q32009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20090701') ALTER PARTITION SCHEME date_part_scheme NEXT USED q42009 ALTER PARTITION FUNCTION date_part_func() SPLIT RANGE('20091001')
NOTE: It is considered a best practice to have your right and left most partitions to be empty. This is so that when you split it, it will not be a very intensive operation. In other words, the end partitions you want to have those empty so that the split can happen very quickly.
Insert records to the table to see if it goes to the new partitions
INSERT INTO invoices VALUES(1,'20091204',5000) INSERT INTO invoices VALUES(2,'20090202',3000) INSERT INTO invoices VALUES(3,'20090803',5000)
Cheers!
