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!
