Partitioning

Here, is a link to Zhong's page partition info

and here is the example script:

partitioning

create schema Archive
go



Alter database CommunityAssist
Add FileGroup F1
Alter database CommunityAssist
Add FileGroup F2
Alter database CommunityAssist
Add Filegroup F3
Go



Alter Database CommunityAssist
Add File
(Name=FG1_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_3.ndf',
size=2MB)
to fileGroup F1
Alter Database CommunityAssist
Add File
(Name=FG_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_4.ndf',
size=2MB)
to fileGroup F2
Alter Database CommunityAssist
Add File
(Name=FG3_da, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CommunityAssist_5.ndf',
size=2MB)
to fileGroup F3
GO



Create partition function ServiceGrantFunc (datetime)
AS
Range Right
For Values('1/1/2010','1/1/2011')
Go



Create partition scheme ServiceGrantScheme
As
Partition ServiceGrantFunc
To (F1,F2,f3)
Go



Create table Archive.ServiceGrant
(
Grantkey int not null,
GrantAmount money,
GrantDate datetime not null,
PersonKey int,
ServiceKey int,
EmployeeKey int)
on ServiceGrantScheme(GrantDate)

Go


Create clustered index ix_GrantDate
on Archive.ServiceGrant(GrantDate)
on ServiceGrantScheme(GrantDate)
Go



Alter table Archive.ServiceGrant
add constraint PK_GrantKey Primary Key nonclustered(GrantKey,GrantDate)



Insert into Archive.ServiceGrant(GrantKey,GrantAmount, GrantDate, PersonKey, ServiceKey, EmployeeKey)
Values(1,500,'2/3/2009',1,2,1),
(2,100,'2/3/2011',1,2,1),
(3,50,'4/1/2010',1,2,1)



Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=1

Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=2

Select * from Archive.ServiceGrant
where $partition.ServiceGrantFunc(GrantDate)=3