I have found that table partitioning in SQL Server is not widely used or understood. Table partitioning is a feature available with the Enterprise editions of SQL Server. It allows you to split large database tables into separate parts. There are many benefits to table partitioning; two examples are reducing table contention and more efficient query processing. Table partitioning in SQL 2005 was only available through the creation of T-SQL scripts.
SQL Server 2008 now provides a table partitioning wizard within SQL management studio 2008 to make partitioning tables easier. Once you select a table to partition, you can right-click that table and select Storage | Create Partition.
The first step is deciding on which column to use to partition your table. Depending on the table, this is often a date related field, or perhaps a geographic field. Using the Sales.SalesOrderHeader table in the AdventureWork2008 database, I’ll choose the OrderDate field.
Next you create a partition function (or use an existing one). The partition function can be considered a mapping of data in the table allowing you to specify ranges of data for the created partitios.
Then, you create a partition schema (or select an existing one). The partition schema can be considered a mapping of the partitions to Filegroups.
Once on the Map Partitions screen, you can either manually specify your partitions, or you can use the “Set boundaries…” button on the wizard page to have the wizard generate them for you. In this example, pressing the button gives you the following:
It determines the data in the table, and asks you to specify a date range for the partition. Once you hit okay, you go back to the Map Partitions screen with the filegroups and boundaries specified. If you wish you can further change these. Press the “Estimate storage” button to give you rowcounts and storage requirements for each partition. You must create one additional filegroup to handle data falling outside the last boundary.
Finally you are presented with options to generate the corresponding T-SQL script, or to update the table immediately.
The resulting TSQL script
USE [AdventureWorks2008]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [OrderDate](datetime) AS RANGE LEFT FOR VALUES (N'2002-07-01T00:00:00', N'2003-07-01T00:00:00', N'2004-07-01T00:00:00', N'2005-07-01T00:00:00')
CREATE PARTITION SCHEME [OrderDate] AS PARTITION [OrderDate] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
ALTER TABLE [Sales].[SalesOrderDetail] DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
ALTER TABLE [Sales].[SalesOrderHeaderSalesReason] DROP CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID]
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID]
ALTER TABLE [Sales].[SalesOrderHeader] ADD CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY NONCLUSTERED
(
[SalesOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE CLUSTERED INDEX [ClusteredIndex_on_OrderDate_633664970504317085] ON [Sales].[SalesOrderHeader]
(
[OrderDate]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [OrderDate]([OrderDate])
DROP INDEX [ClusteredIndex_on_OrderDate_633664970504317085] ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF )
ALTER TABLE [Sales].[SalesOrderDetail] WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
ALTER TABLE [Sales].[SalesOrderDetail] CHECK CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
ALTER TABLE [Sales].[SalesOrderHeaderSalesReason] WITH CHECK ADD CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE CASCADE
ALTER TABLE [Sales].[SalesOrderHeaderSalesReason] CHECK CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID]
COMMIT TRANSACTION
Once the partitions are created we can look at the partitions to see how many rows are in them. You’ll notice that the actual rows don’t exactly match the estimated rows form the wizard (but then that was only an estimate).
So in conclusion, the “Create Partition” Wizard in SQL Server 2008 is a welcome addition to the toolset, and simplifies the creation of partitioned tables.