SQL Server 2008 – Table Partitioning

Hi folks! That’s my first adventure published here on my blog, now giving a little attention to SQL Server 2008 and its partitioning tables resourses. SQL Server in its new release, put on the market many new features and partitioning is one of that got my attention. This resource is so simple to planing, use and implements. On the source below, you’ll see facilities when create a partition scheme to control the way data will be storage on filegroups and how create new table using ON to speak to SQL Server what function uses to make column partition.

Partition function doesn’t have nothing of user-defined function (UDF), but, you can create one and inherits to many tables that resides on the same partition schema. You can observe on source below, when I created a new partition schema, I join partition function and partition schema, besides metion filegroups where data will be storage.

-- project create by Wagner Bianchi
-- e-mail: wagnerbianchijr@gmail.com
-- twiter: @wagnerbianchi

-- begin job creating a new database
create database wagner
go

-- change context to our new database
use wagner
go

-- creating filgroups for each created schema
alter database wagner add filegroup FG1
alter database wagner add filegroup FG2
alter database wagner add filegroup FG3
go

-- adding files for each created filegroup
alter database wagner
add file (name = N'wagner_marcos_fg1', filename = N'C:\test\wagner_marcos_fg1.ndf')
to filegroup fg1
go
alter database wagner
add file (name = N'wagner_bianchi_fg2', filename = N'C:\test\wagner_bianchi_fg2.ndf')
to filegroup fg2
go
alter database wagner
add file (name = N'wagner_junior_fg3', filename = N'C:\test\wagner_junior_fg3.ndf')
to filegroup fg3
go

-- selecting created files on sys.sysdatabases (metadata)
select name, filename from sys.sysdatabases
go

-- creating schemas
create schema marcos
go
create schema bianchi
go
create schema junior
go

-- creating partition function to use on all tables - modular partition functions;
-- we will join partition function and partition scheme to partitioned table;
-- after execute code below, we'll have two partitions on table resident
-- on partition scheme and have a column partitioned
create partition function partDatetimeFunction (datetime) as
range right for values ('2010-01-01 00:00:00')
go

-- create partition schema on filegroups previously created
create partition scheme partSchemOfMine
as partition partDatetimeFunction
to (fg1,fg2,fg3)
go
-- Partition scheme 'partSchemOfMine' has been created successfully.
-- 'FG3' is marked as the next used filegroup in partition scheme 'partSchemOfMine'.
-- NEXT USED is the next file to be used after the actual - FG1,FG2

-- creating database.schema.tables
create table wagner.marcos.t1 (
id int not null identity(1,1),
[date] datetime not null
) on partSchemOfMine([date]);
go
create table wagner.bianchi.t1 (
id int not null identity(1,1),
[date] datetime not null
) on partSchemOfMine([date]);
go
create table wagner.junior.t1 (
id int not null identity(1,1),
[date] datetime not null
) on partSchemOfMine([date]);
go

-- testing project charging some rows into table wagner.bianchi.t1
insert into wagner.bianchi.t1 (date) values ('2008-05-22 10:25:23')
insert into wagner.bianchi.t1 (date) values ('2009-04-28 10:25:23') 
insert into wagner.bianchi.t1 (date) values ('2010-05-18 10:25:23')
insert into wagner.bianchi.t1 (date) values ('2010-07-05 10:25:23') 

-- query for metadata information
select 'wagner' as [database], a.name as [schema], b.name as [table name],
c.partition_number as [part #], c.rows [QtdRows]
from sys.schemas as a inner join sys.tables as b
on a.schema_id = b.schema_id inner join sys.partitions as c
on b.object_id = c.object_id
where a.name in('marcos','bianchi','junior')

More references:

• http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx
• http://msdn.microsoft.com/en-us/library/ms187526.aspx