Click here to monitor SSC
  • Av rating:
  • Total votes: 51
  • Total comments: 10
Barry King

SQL Server Partitioning without Enterprise Edition

10 December 2009

 Table partitioning is a valuable technique for managing very large database tables. It has always been possible with SQL Server, even if slightly cumbersome. SQL Server 7.0 introduced partitioning through partitioned views: These were combined with constraints to allow the query optimizer to remove irrelevant tables from the query plan and reduce the overall plan cost when a UNIONed view accessed multiple tables. As Barry King explains, this is still a valuable feature that works in every current version of SQL Server.

What is Partitioning and Why use it?

Table Partitioning is a way to segregate your data using a particular scheme or set of rules. The main reason for doing this is query performance; when you partition data and you have chosen a good partitioning scheme relevant to your data it means that as long as you stick to the partitioning scheme rules your query only "touches" the data in that partition. It was introduced in SQL 2005.

Imagine a 60 million row table, querying this depending on the complexity of the query; number of joins and criteria etc may take a considerable amount of time. In the world of partitioning , the number of rows queried is reduced according the granularity of the partitioning scheme.

There is of course some downsides to this. The first is that queries that cross the partition rules will take longer, arguably not longer than querying the non-partitioned table but this will be dependent on the number of partitions and again the granularity.

The second ,for small to medium size companies, most  important problem is that true partitioning requires SQL Server Enterprise Edition. The current big daddy of the SQL family. This can be a very hard financial pill to swallow. Some may argue, including Microsoft,  that if you are dealing with such a large volume of data that you must be a large, financially rich company and that this is not a barrier. 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.

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.

To achieve a version of partitioning without Enterprise Edition turns out to be relatively simple and offers nearly all of the benefits of true partitioning.

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 does when insert happens and what query plan is used to select the data.

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 a partitioned view to work fully (as in allow inserts) , your 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 the 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.

In this 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, 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.

If you are interested in using true Table Partitioning, I'd recommend the article Partitioning in SQL Server 2008 on SQL Server Central by Muhammad Shujaat Siddiqi.

Barry King

Author profile:

Barry has been working for Total Jobs Group as a Senior Database Developer for the last 2+ years, having come from a consultancy background where he worked for many high profile companies including UK based charity Marie Curie Cancer Care. He has a passion for anything geeky but focuses primarily on database related technologies.

Search for other articles by Barry King

Rate this article:   Avg rating: from a total of 51 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Great article
Posted by: mcflyamorim (view profile)
Posted on: Thursday, December 10, 2009 at 1:07 PM
Message: Congrats Barry, simple-read, great Stuff.

Cheers

Subject: Partitioned views
Posted by: Phil Factor (view profile)
Posted on: Monday, December 14, 2009 at 3:20 AM
Message: Barry has done well to introduce a neglected feature of SQL Server. Make sure that, when you implement partitioning, you follow the BOL entry for the version of SQL Server exactly, for the details of implementation, particularly when doing distributed partitioning. The fussier you are about detail, the better the outcome.

Thanks, Barry, for writing so clearly and from direct experience with the feature.

Subject: The importance of partitioning
Posted by: Ian Dickson (not signed in)
Posted on: Monday, December 14, 2009 at 5:01 AM
Message: A great article that clearly explains the benefits and importance of partitioning.

I will recommend others I know who are responsible for these things in their business read Barry's article.

Thank you for sharing it.

Subject: Distributed Partitioned Databases
Posted by: Anonymous (not signed in)
Posted on: Monday, December 14, 2009 at 9:07 AM
Message: Good article, but maybe Barry will explain how or why anyone would want to use Distributed Partitioned Views, next.

This article is Partitioning out a single Table, what if you have an entire database to partition in the same environment?

And don't get it confused with Mirroring, either. Just imagine if you have 6 or more Web Servers hitting this database, Reporting, OLTP daily processing, etc.

Would'nt that be a more interesting article than Partitioning out 1 single table?

C'mon Barry lets do an article about Horizontal Partitioning...

So those of us who really know databases can really enjoy it. Take a bold step for other writers who constantly write entry level articles, for the newbie in the house.

What about us?

We read too you know.



Subject: The main reason ?
Posted by: cranfield (view profile)
Posted on: Monday, December 14, 2009 at 9:27 AM
Message: I have worked extensively with partitioned views and partitioning and in both cases the overriding reason for use is manageability not query performance. Sliding window scenarios work very well with both.

Subject: The main reason ?
Posted by: cranfield (view profile)
Posted on: Monday, December 14, 2009 at 10:28 AM
Message: I have worked extensively with partitioned views and partitioning and in both cases the overriding reason for use is manageability not query performance. Sliding window scenarios work very well with both.

Subject: Very good and easy approach.
Posted by: rambarat (view profile)
Posted on: Monday, January 25, 2010 at 8:05 AM
Message: It is really usefull in scenario where you want to put the data in history table and query only for report and display purpose.

Subject: not cleared with example
Posted by: ashish.kuriyal (view profile)
Posted on: Monday, February 15, 2010 at 12:49 PM
Message: I dont either i am reading it correctly or not but my concerns :-
1) You created table Visit and then drop it by saying "Now, if you created that table; drop it." :) :)
2) Then you created three table visit_a/b/c
3) How can you insert values using tsql "INSERT INTO Visit" while visit already dropped.

I dont know what I am missing..please explain

Subject: third party solutions for partitions on standard edition
Posted by: liorb (view profile)
Posted on: Sunday, November 04, 2012 at 7:53 AM
Message: Does anyone can recommend a third party solution for partitions on standard edition database?
I found a product called Rewius which claim to provide this ability, i wonder if there are any other products

Subject: partitioned
Posted by: ShaikBaazi (view profile)
Posted on: Monday, February 25, 2013 at 5:36 AM
Message: it is really fun !!, After partitioned, i observed it is time taken in Partitioned tables compared "Un Partitioned" Table.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.