Partitioning is an Enterprise only feature and, if you happen to be lucky enough to be using Enterprise, it can be an incredibly powerful and useful feature.
It’s also pretty simple to implement once you know what you’re doing… and therefore this is a quick guide.
Partitioning effectively enables you to control what would otherwise be a very large table by effectively splitting it into smaller tables under the covers and as we know, smaller tables are usually much faster to process.
So how do we go about this?
There are a few stages involved… firstly we need to create a partition function.
A partition function is what we use in order to specify how we want to split up our table. For example, you might want to partition on Date or maybe on ID.
In this case I’m going to partition on Date.
where name = 'testPartitionFunction'
drop partition function testPartitionFunction
create partition function testPartitionFunction(date)
as range left
for values('2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01')
Note that if we used RIGHT then the boundary ends on the right… ie. In the case above anything up to and including 2008-01-01 would go into partition 1.
As we used LEFT then anything up to and including 2007-12-31 would go into the first partition.
Also note that each end is unbounded. Therefore 4 boundaries provides 5 partitions.
Next, before we try to create a table, we need to create a partition Scheme. A Scheme is required in order to map partitions to filegroups.
This means that, if we wanted, we could map each partition to a different physical drive and get even better performance on our large tables. In my case I only have 1 drive and therefore I’ve only bothered with one filegroup, so I’m going to map everything to that.
where name = 'testPartitionScheme'
drop partition scheme testPartitionScheme
create partition scheme testPartitionScheme
as partition testPartitionFunction
all to ([primary])
So… now we have our partition function mapped to our scheme, mapped in turn to our filegroup. All we need now is our table.
if object_id('testPartition') is not null drop table testPartition
create table testPartition
id int identity,
totalDue decimal(19, 8),
constraint pk_testPartition primary key clustered(id, orderDate)
) on testPartitionScheme(orderDate)
As you can see, we create the table in the normal way except that we specified our partition scheme.
And we’re done.
Lastly all we need to do is throw some data into the table and read from it… we do all this in the normal ways… however, to end this post let’s see how this looks under the covers:
insert into testPartition
select orderDate, AccountNumber, PurchaseOrderNumber, TotalDue
where orderDate between '2007-12-01' and '2008-05-01'
where object_id = object_id('testPartition')
And there you can see the data in the clustered index (always ID 1) has been split between the 5 partitions as we expected.
And that’s that.