Partitioning large tables in SQL Server Enterprise

SQL Server Enterprise allows user to partition large tables and indexes. Partitioning can be used for a variety of reasons:

* You can split a large table into 2 or more partitions to improve performance. Optionally, each partition can also be stored in a separate SQL Server file group.

* You can use partitioning as an "archiving function". You create 2 tables that have the same design: a current table and an archive table. You then define partitions on each table. Every month, you can "move" data from the current table to the archive table using partitions. But the data doesn't really move. Rather, the "partition" meta data changes but the data stays where it is. This can be significantly faster than actually moving the data with a "select into ..." command. See Microsoft's example: http://technet.microsoft.com/en-us/library/ms191174(SQL.100).aspx

* You can run database maintenance routines on a partition rather than the entire table, thus speeding up the maintenance routine (assuming you don't need to run the maintenance on the entire table)

Steps to create a partition:

Step 1 - Add a filegroup to the database

ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]

Step 2 - Add files to the new filegroup

ALTER DATABASE AdventureWorks

ADD FILE

(NAME = N'2003Q3', FILENAME = N'C:\AdventureWorks\2003Q3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [2003Q3]

Step 3 - Create a partition function that defines how your table will be split into different partitions

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime) AS RANGE LEFT FOR VALUES ('20000930 23:59:59.997', '20001231 23:59:59.997', '20010331 23:59:59.997', '20010630 23:59:59.997')

Step 4 - Create partition scheme to define which filegroups each partition should be stored on

CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION OrderDateRangePFN TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])

Step 5 - Create your table and assign it to your partition scheme

CREATE TABLE [dbo].[OrdersRange] ( [PurchaseOrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL , [RevisionNumber] [tinyint] NULL , [ModifiedDate] [datetime] NULL , [ShipMethodID] [tinyint] NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NOT NULL CONSTRAINT OrdersRangeYear CHECK ([OrderDate] >= '20030701' AND [OrderDate] <= '20040630 11:59:59.997'), [TotalDue] [money] NULL ) ON OrderDatePScheme (OrderDate) GO

STEPS TO ALTER PARTITION:

ALTER PARTITION FUNCTION OrderDateRangePFN() SPLIT RANGE(19999)

ALTER PARTITION FUNCTION OrderDateRangePFN() MERGE RANGE(19999)

If archiving data by moving it from a current table to an archive table:

Alter Table Current SWITCH partition1 to Archive partition2

This doesn't "physically" move the data but accomplishes the same thing.