<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Partition A table using Computed column

Published on
11,189 Points
5,189 Views
Last Modified:
Approved
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);

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
0
Comment
Author:Aaron Shilo
0 Comments

Featured Post

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month