Sql Server 2005 Partitioning

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

-- 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')

As you can see in the last lines, you can also query system metadata to see in which partition your data has been placed.

Have fun!

posted on Monday, July 18, 2005 9:41 PM Print