Tag: Database Design

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 3^rd 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!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter