PartitioningIntroduced in SQL Server 2005, partitioning allows data in tables to be spread over multiple files in an organised way, thus providing a simple method of archiving data, enabling filegroup back-ups and, if the files are on different physical disks, can improve performance for large databases. This feature is only available in Enterprise, Developer and Evaluation editions of SQL Server.
To partition a database, you need to do the following:-
-
Create multiple filegroups
-
Create a partition function (the values to be used for partitioning)
-
Create a partition scheme to associate the partition function to the filegroups
-
Assign a partition scheme to a table or index
When assigning a partition to a table, it is best practice to align any associated indices to allow for filegroup back-ups and archiving.
Once the table has been partitioned, the split and merge operators can be used to manage the partitions and the switch operator can be used to instantly move the partition from one table to another e.g. to an archive table.
