How to Partition a Table
Published: 15th April 2015
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.

use AdventureWorks2012
go

if exists
(
  
select *
  
from sys.partition_functions
  
where name = 'testPartitionFunction'
)
begin
   drop
partition function testPartitionFunction
end

create
partition function testPartitionFunction(date)
  
as range left
  
for values('2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01')
go


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.

if exists
(
  
select *
  
from sys.partition_schemes
  
where name = 'testPartitionScheme'
)
begin
   drop
partition scheme testPartitionScheme
end

create
partition scheme testPartitionScheme
  
as partition testPartitionFunction
  
all to ([primary])
go


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
go

create table testPartition
(
  
id int identity,
  
orderDate date,
  
accountNumber varchar(20),
  
purchaseOrderNumber varchar(20),
  
totalDue decimal(19, 8),
  
constraint pk_testPartition primary key clustered(id, orderDate)
)
on testPartitionScheme(orderDate)
go


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
from sales.SalesOrderHeader
where orderDate between '2007-12-01' and '2008-05-01'
go

select *
from sys.partitions
where object_id = object_id('testPartition')
go


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.
Comments:
NB: Comments will only appear once they have been moderated.