Partition A table using Computed column

First Of All a little bit about Partitions In sql server.

Partitioned tables are a new feature available in SQL Server version 2005, and are mainly for improving the performance of querying large tables.

I look at partition table like the Encyclopedia Britannica; imagine that Encyclopedia Britannica was one very thick heavy book :-)
That would be very hard to use, instead Encyclopedia Britannica is broken down into volumes logically separated by the Alphabet.

So when you look for a specific value likes lest say "American Alligator" you will pick up the "A to C" volume and use it.

We can do the same thing with our large table’s break them down into physically different parts but always access them through one logical entity THE TABLE.
This means our query doesn’t change, we keep querying the same table but sql server stores the information in Partitions As specified by us using A partition Function.

So why would you want to use this feature?

Well let’s say you have a table that holds sales information and this table is very large, so what we usually do is every Quarter create a new table and archive the older table , to access the data (typically via a view) need to retrieve information from all historical tables with the UNION operator.

This is a high maintenance not very efficient way to do things but until SS2005 we had no other choice really. Now we can have one table with different partitions with every partition holding the relevant data. It spreads the actual data over a number of physical areas to help improve performance by maximising available disk and targetting more relevant data in each query. The advantage is the Query Engine manages it for you (after you set it up) so no more union queries from multiple tables.

A while ago I was asked to partition a table. Sounds simple, except that I needed to use a computed column. The computed column returned the hour for every row based on a DATETIME type column.

So first up, YES, you can partition a table based on a Computed column. You need to make sure the data computed is deterministic. To achieve this I had to change the computed Column  definition to " PERSISTED ".

From SQL Server Books OnLine... PERSISTED: Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated.

From there on it was plain and simple see my sample code :

-- step 1 is to create a partition function. Basically telling the database engine what data it needs to look at and how to divide it up
                      
                      CREATE PARTITION FUNCTION PFN_Hourly (int)
                      AS RANGE RIGHT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23);
                      
                      -- step 2 is to create a scheme for the partitions. This is where you can tell it which filegroup to use. For this example we will just use PRIMARY.
                      
                      CREATE PARTITION SCHEME PSN_Hourly 
                      AS PARTITION PFN_Hourly
                      ALL TO ([PRIMARY]);
                      
                      -- step 3 is to create the table and the computed column and refer that column to the scheme above
                      
                      CREATE TABLE PartByHour (ID Int Identity, LogInDate DateTime ,HourOfDay as DATEPART(hour, LogInDate) PERSISTED) on PSN_Hourly (HourOfDay);

Select all Open in new window

Now the final step - step 4 would be to create indexes. The partition column needs to be part of the unique index, and if that is the Primary Key then cannot be NULL. But that might be covered in a future Article.

In the meantime read up on partitions : http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx