Partitioning in SQL Server 2005

A very interesting an powerful feature of Sql Server 2005 is called Partitioning. In a few word this means that you can horizontally partition the data in your table, thus deciding in which filegroup each rows must be placed.

This allows you to operate on a partition even with performace critical operation, such as reindexing, without affecting the others. In addition, during restore, as soon a partition is available, all the data in that partition are available for quering, even if the restore is not yet fully completed.

Here a simple script to begin to make some test on your own:

use adventureworks
go

-- Setup a clean system
drop partition scheme YearPS;
drop partition function YearPF;

-- Create a partitioning functions.
-- Here we're creating two partitions based on date values: all values from and after 2005-01-01
-- will go in the second partition and al the values before goes in the first one.
create partition function YearPF(datetime)
as range right for values ('20050101');

-- Now we need to add filegroups that will contains partitioned values
alter database AdventureWorks add filegroup YearFG1;
alter database AdventureWorks add filegroup YearFG2;

-- Now we need to add file to filegroups
alter database AdventureWorks add file (name = 'YearF1', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF1.ndf') to filegroup YearFG1;
alter database AdventureWorks add file (name = 'YearF2', filename = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdvWorksF2.ndf') to filegroup YearFG2;

-- Here we associate the partition function to
-- the created filegroup via a Partitioning Scheme
create partition scheme YearPS
as partition YearPF to (YearFG1, YearFG2)

-- Now just create a table that uses the particion scheme
create table PartitionedOrders
(
Id int not null identity(1,1),
DueDate DateTime not null,
) on YearPS(DueDate)

-- And now we just have to use the table!
insert into PartitionedOrders values(getdate()-200)
insert into PartitionedOrders values(getdate()-100)
insert into PartitionedOrders values(getdate())
insert into PartitionedOrders values(getdate()+100)
insert into PartitionedOrders values(getdate()+200)

-- Now we want to see where our values has falled
select *, $partition.YearPF(DueDate) from PartitionedOrders

-- You can also view how many partitions we did
select * from sys.partitions where object_id = object_id('PartitionedOrders')