Creating Partitions Using Dynamic SQL

Recently I wrote a post on partition switching, however, one of the big pieces I left out was how to go about properly partitioning your database tables. Unfortunately, partitioning strategy can vary greatly, but generally your partitions will be aligned with your clustered index (quite frequently date). I recommend reading the Fast Track reference guide for guidance on how to layout file groups efficiently.

So now that we have that clear, the next question is — how is the data actually partitioned? Well there is three pieces to be aware of, the first is the partition function. A partition function is used to map the rows of a table into specific partitions (it’s worth noting that you can now have up to 15,000 partitions per table in SQL Server 2012, versus 1,000 per table in SQL 2005 & 2008). In its simplest form you can define your partition function with a rather simple SQL statement.

--MSDN Example 

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); 

--This example creates 4 partitions (yes four, not three as it would appear to create) for an integer value with each number in the range representing the left side of the boundary:

Partition # Column Values 1 Column 2 1 < Column 3 100 < Column 4 Column > 1000

However, depending the size of our tables and complexity of our data sometimes a model this simplistic doesn’t always work. In these situations we can use dynamic SQL to create our partition function. In this next example we are creating a partition for every month in a fact table which has an integer key for date. Our date key format is YYYYMMDD.

--Create date partition function with increment by month. 

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION FactSalesPartitionFunction (int) AS RANGE RIGHT FOR VALUES ('; 

DECLARE @i datetime2 = '19900101'; 
WHILE @i < '20500101' 
BEGIN 
     SET @DatePartitionFunction += '''' + CAST(DATEPART(year, @i)*10000 + DATEPART(month, @i)*100 + DATEPART(day, @i) AS varchar(8)) + '''' + N', '; 
     SET @i = DATEADD(MM, 1, @i); 
END 

SET @DatePartitionFunction += '''' + CAST(DATEPART(year, @i)*10000 + DATEPART(month, @i)*100 + DATEPART(day, @i) AS varchar(8)) + '''' + N');'; 

EXEC sp_executesql @DatePartitionFunction; 
GO

Once we have our partition function created we then need to create a partition scheme which allows us to map our database partitions created by the partition function to file groups. Again this can be as simple or complex as you would like it to. You can map all your partitions to one file group:

CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 ALL TO ( testfg );

You can also map each partition to a different file group (note: you have to have one file group specified for each partition, however, you are allowed to reuse file groups):

--MSDN Example 

CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO ( test1fg, test2fg, test3fg, test1fg );

Or, you can once again write dynamic SQL to lay out your partitions into file groups. In this example we take the 720 partitions created in our previous partition function and evenly spread them across 12 file groups:

DECLARE @DatePartitionScheme nvarchar(max) = N'CREATE PARTITION SCHEME FactSalesPartitionScheme AS PARTITION FactSalesPartitionFunction TO ('; 
DECLARE @i datetime2 = '19900101'; 
DECLARE @m int = 12 
WHILE @i <= '20500101' 
BEGIN 
     SET @DatePartitionScheme += '''' + 'Sales_' + CAST((@m % 12) + 1 AS varchar(2)) + '''' + N', '; 
     SET @i = DATEADD(MM, 1, @i); 
     SET @m = @m + 1 
END 
SET @DatePartitionScheme += '''' + 'Sales_' + CAST((@m % 12) + 1 AS varchar(2)) + '''' + N');'; 

EXEC sp_executesql @DatePartitionScheme;
GO

Once the partition function and the partition scheme is created the next task is to either recreate your clustered index on partition scheme or create a new table on the partition scheme –

--Creating a clustered index using a partition scheme 

CREATE CLUSTERED INDEX [ixTimeKeyClustered] ON [Sales].[FactSales] ( [TimeKey] ASC ) 
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) 
     ON [FactSalesPartitionScheme]([TimeKey]) 

GO 

--Creating a table (heap) using a partition scheme (MSDN Example) CREATE TABLE PartitionedTable (col1 int, col2 char(10)) 
     ON myRangePS1 (col1); 

GO