SQL Server supports table partitioning, whereby a table can be carved into as many as 15,000 pieces, with each residing on its own fi legroup. Each fi legroup can be independently backed up. Different fi legroups can also be located on different storage. For example, current data can be held on fast disks, possibly even on solid-state disks. Older/archive data can then be moved to larger, slower disks and more easily deleted when the data is no longer needed. The deletion of a partition can be achieved extremely quickly, and with virtually no impact to queries against the current data. Look at the following partitioning example in which each partition contains one month’s data. With table partitioning, a new empty partition is created when the next monthly data becomes avaicontent">
SQL Server Table Partitioning
lable. Then the oldest partition can be switched out into a table and moved to an archive table monthly. The basic steps to create a table partition are as follows:- Create a partition function that describes how you want the data partitioned.
- Create a partition schema that maps the pieces to the fi legroups.
- Create one or more tables using the partition scheme.
Following is an example of creating a partition table using a monthly sliding window:
--Create partition functionCREATE
PARTITION FUNCTION
[OrderDateRangePFN](datetime)AS
RANGE RIGHTFOR
VALUES
(N'2014-01-01 00:00:00', N'2017-02-01 00:00:00', N'2017-03-01 00:00:00',N'2017-04-01 00:00:00');--Create partition schemeCREATE
PARTITION SCHEME [OrderDatePScheme]AS
PARTITION [OrderDateRangePFN]TO
([filegroup1], [filegroup2], [filegroup3], [filegroup4], [filegroup5]);--Create partitioned table SalesOrderHeaderCREATE
TABLE
[dbo].[SalesOrderHeader]([SalesOrderID] [int]
NULL,[RevisionNumber] [tinyint]
NOT NULL,[OrderDate] [datetime]
NOT NULL,[DueDate] [datetime]
NOT NULL,[ShipDate] [datetime]
NULL,[Status] [tinyint]
NOT NULL)
ON
[OrderDatePScheme]([OrderDate]);This example places each partition on a different fi legroup. Splitting and merging partitions requires data movement. You can achieve high-speed splits and merges without table locking or reducing concurrency if you place the partitions on the same fi legroup. When partitions are on the same fi legroup, switching out a partition or merging is only a schema change and occurs quickly. Several other smaller restrictions.