SQL Server Table Partitioning without Enterprise Edition

I am going to show a way to have table partitioning in SQL Server databases without the need for the Enterprise Edition of SQL Server that can easily to used in 2000, 2005 or 2008.

As some of you know true Table Partioning was introduced in 2005 but this requires the Enterprise Edition, this is unfortunate as that particular version is rather expensive and out of reach for many small-medium sized businesses. However, the ability to partition/split data is a common request – especially as data grows and there are natural splits within your schema to take advantage of.

To achieve partitioning without EE it turns out is relatively simple and offers nearly all of the benefits of true partioning.

Lets say we have a ficticious company called NurseBank. NurseBank is operated in 3 Regions; England, Scotland and Wales. Every time a Nurse visits a Patient a Visit record is created and this is assigned to a Region who deals with the booking of Nurses for that Region.

In our original schema we would have had something like this for the Visit table:

CREATE TABLE Visit

(

VisitID INT IDENTITY(1,1) NOT NULL,
RegionId INT NOT NULL,
VisitDate DATETIME NOT NULL,
NurseId INT NOT NULL,
ClientId INT NOT NULL,
CONSTRAINT PK_Visit_VisitID PRIMARY KEY (VisitId)

)

Now, if you created that table; drop it. We are going to create 3 new tables to cater for the new partitions.

CREATE TABLE dbo.Visit_England

(

VisitId INT NOT NULL ,
RegionId INT NOT NULL
CONSTRAINT CK_Visit_England CHECK (RegionId = 1),
VisitDate DATETIME NOT NULL,
NurseId INT NOT NULL,
ClientId INT NOT NULL,
CONSTRAINT PK_Visit_England PRIMARY KEY (VisitId,RegionId)

)

CREATE TABLE dbo.Visit_Scotland

(

VisitId INT NOT NULL ,
RegionId INT NOT NULL
CONSTRAINT CK_Visit_Scotland CHECK (RegionId = 2),
VisitDate DATETIME NOT NULL,
NurseId INT NOT NULL,
ClientId INT NOT NULL,
CONSTRAINT PK_Visit_Scotland PRIMARY KEY (VisitId,RegionId)

)

CREATE TABLE dbo.Visit_Wales

(

VisitId INT NOT NULL ,
RegionId INT NOT NULL
CONSTRAINT CK_Visit_Wales CHECK (RegionId = 3),
VisitDate DATETIME NOT NULL,
NurseId INT NOT NULL,
ClientId INT NOT NULL,
CONSTRAINT PK_Visit_Wales PRIMARY KEY (VisitId,RegionId)

)

With these three tables created. We can create the Partitioned View that will allow us to select, update and insert data into the partitions.

CREATE VIEW dbo.Visit
WITH SCHEMABINDING
AS
SELECT VisitId, RegionId, VisitDate,NurseId, ClientId FROM
dbo.Visit_Scotland
UNION ALL
SELECT VisitId, RegionId, VisitDate,NurseId, ClientId FROM
dbo.Visit_England
UNION ALL
SELECT VisitId, RegionId, VisitDate,NurseId, ClientId FROM
dbo.Visit_Wales

You can now insert data into the Visit view as we would have done previously with the original table (with one exception that I will cover shortly).

 

– INSERT INTO England Region Visit table
INSERT INTO Visit (VisitId, RegionId, VisitDate, NurseId, ClientId)
VALUES (1000,1,GETDATE(),4122,5211)

– INSERT INTO Scotland Region Visit table
INSERT INTO Visit (VisitId, RegionId, VisitDate, NurseId, ClientId)
VALUES (1001,2,GETDATE(),4123,5212)

The interesting and important aspect is what SQL Server makes of all of this and what query plan is used.

SELECT VisitID FROM Visit WHERE RegionId = 1

This will produce the following query plan:

As you can see it correctly uses the Visit_England partitioned table. If the Region is omitted from the WHERE clause, the query plan is vastly different and of course cannot determine the correct table to use so basically it UNION joins them all as shown below.

I don’t think I need to say but this is bad. However, the whole point of partitioning the data is when you have a need to do so (large volumes of data) and you have a viable partitioning scheme that will avoid the above type of plan as much as possible.

Lastly, I wanted to mention the exception I hinted at before.

For the partitioned view to fully work (as in allow inserts), the partitioned table cant have an identity column.

What! I hear you say, whats the point of that? How I can use this?. Well, in most situations the partitioning of the data is done to improve READ performance of queries. If this is the case then the perfect scenario for this type of partitioning is to partition the data on a replicated separate database. The way this works is that you have your inserts performed on your main database or cluster and when the data is replicated to the other database (lets call this the readonly but it isn’t a physical readonly – its just we wont be doing writes on it) the replication procedure is modified to insert into the Partitioned View and the original table thats replicated. That way you don’t need an identity column on the partitioned tables as this would be known and passed into the table via replication.

I hope you find this example useful, if you have any comments or questions regarding this please feel free to contact me via this blog. This is by no means the only way to improve performance and you really must consider all available options but I thought this was a useful and relatively painless method to try and one we currently use in our business.

Service Broker Deployment Numero Uno!

We have just deployed my first Service Broker project to the live environment and touch wood everything appears to have gone ok. Sure it took a little massaging to get it through and we certainly learned a few things along the way but thats all part of it right.

Working for a well known job board we are using service broker in conjunction with SQL CLR to do asynnchronous string manipulations to job adverts.  Orignally this was something we did through replication but it became clear very quickly that fast as CLR is, replication latency would be effected.  So instead we had a re-think and moved the process offline.  We’re sure that there are many other applications for Service Broker within our business now.