Why Use a SQL Server Table Partition
- Data access is more efficient and quicker, do to the volume of records
- Maintenance operations can run on one or more partitions. For example, rebuilding an index on a table that is frequently update
CREATE PARTITION FUNCTION [pf_SalesOrdHeader_byOrderDate](datetime ) AS RANGE RIGHT FOR VALUES (N’2005-01-01T00:00:00′, N’2006-01-01T00:00:00′,
N’2007-01-01T00:00:00′, N’2008-01-01T00:00:00′, N’2009-01-01T00:00:00′)
I have created 3 filegroups in the AdventureWorks database that each have a new datafile. I want to map my data from 2005 to 2009 into these 3 file groups. To do this, I create a partition scheme to map the dates from the partition function to the file groups in my database. SalesHeader_fg1 will store data for years <= 2005 and <2006. SalesHeader_fg2 will store data for years >=2006 and <2008. SalesHeader_fg3 will store data >=2008.
CREATE PARTITION SCHEME [ps_SalesOrdHeader_byYear] AS PARTITION [pf_SalesOrdHeader_byOrderDate]
TO ([SalesHeader_fg1] , [SalesHeader_fg1] , [SalesHeader_fg2] , [SalesHeader_fg2] , [SalesHeader_fg3], [SalesHeader_fg3])
Now that the partition function and partition schema are created, I need to modify the Sales.SalesOrderHeader table to leverage the newly created partition. In SSMS right click on the Sales.SalesOrderHeader to configure the storage for this table. Follow the steps to setup the partitioning and implement it on the table.
This script will modify the table to leverage the partitioning:
CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_SalesOrdHeader_byYear_635501670010470225] ON [Sales].[SalesOrderHeader]
)WITH ( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF) ON [ps_SalesOrdHeader_byYear]([OrderDate] )
After configuring the partition using the above settings you can look at the table properties to see how the partition is setup.
In order to see how the partitioning split the records across the files and filegroups you can run this query:
SELECT OBJECT_SCHEMA_NAME (t. object_id) AS schema_name
For more information on SQL Server Partitions see these links: