Table Partitioning Basics

Partioning objects in SQL Server starts with defining Partition Functions and Partition Schema's. The functions split your data and the schema's place it somewhere. A function can be used by multiple schema's. You start out by creating a function:

CREATE PARTITION FUNCTION [pfSample](datetime)

AS RANGE LEFT

FOR VALUES (1, 100, 1000);

A function must cover all data: from minus infinity to infinity.

You have to choose between RANGE RIGHT and RANGE LEFT. RANGE LEFT is default if you don't specify it.

RANGE RIGHT:

RANGE LEFT:

Things to notice here:

• You always have 1 partition more than values you put in the Partition Function.

• Range Left/Right does nothing more putting the boundary on a side.

     

Range Left/Range Right is an important decision to make. Most people favor Range Left . If you use a sliding window scenario Range Left feels more natural because with Range Right your first partition will be empty. Let me clarify this with the following example:

You want to partition a table on a field that contains a date. You want to make partitions for the last four months. The rest of the data you switch out to an archive table. So you have partitions for March, April, May and June. Your first partition on a Range Right will be dates before March. So your first partition will be empty… The same scenario with a Range Left will make your last partition empty. That's not so bad and could even be useful if you didn't slide your window on time.

When you are satisfied with your partition function you can proceed with the partition schema:

CREATE PARTITION SCHEME [psSample]

AS PARTITION [pfSample]

TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

Schema's tell the function where to put the partitions. In this case, on my laptop, I put all partitions on the primary filegroup. In production environments you will put them on different filegroups of course.

Now you have everything in place to partition a table that has a datetime column. In SQL 2008 you can partition an existing table with a wizard. No such thing in SQL 2005. You have to rename your table, recreate a new on on the partition scheme, and move the data back…..Or you just remove the clustered index and recreate that on the partition scheme

ALTER TABLE Test

ADD CONSTRAINT PK_Test

PRIMARY KEY CLUSTERED(TestDate)

ON psSample(TestDate)

Make sure that one of the columns in your clustered key matches the partitioning column otherwise:

Msg 1908, Level 16, State 1, Line 1

Column 'datum' is partitioning column of the index 'PK_Test2'. Partition columns for a unique index must be a subset of the index key.

On tables with lots of rows partitioning the clustered key might take a while. Think of hours on really big tables. Don't try this on peak hours!

Now you have your partitioned table up and running you have a few commands at your disposal to administer the thing.

First of all you can SPLIT or MERGE a partition.

SPLIT adds a new partition. For example:

ALTER PARTITION FUNCTION pfSample()

SPLIT RANGE(2000);

This piece of T-SQL will create an extra partition that contains rows > 2000. If you get the following error you have to alter your Partition Scheme:

Msg 7707, Level 16, State 1, Line 1

The associated partition function 'pfSample' generates more partitions than there are file groups mentioned in the scheme 'psSample'.

The message says it all: you are out of places to put partitions on in your scheme. So let's add one to the scheme:

ALTER PARTITION SCHEMA psSample

NEXT USED [Primary];

Now you have added an extra partition to your scheme. The split will work now.

Sometimes you want to merge two partitions. That you do with the MERGE statement:

ALTER PARTITION FUNCTION pfSample()

MERGE RANGE(1);

When this completes you have one partition less.

That's it for tonight. I'll do one more post on partitioning and then I cover swithing in and switching out staging tables.