Create Partition Scheme

-- 0. create filegroup and file - if new filegroup/file are required

ALTER DATABASE GlobalSales ADD FILEGROUP PartitionFG

ALTER DATABASE GlobalSales ADD FILE

(

NAME = N'BlaBla' ,

FILENAME = N'C:\FolderA\FolderB\BlaBla.ndf'

)

TO FILEGROUP PartitionFG

-- 1. create partition function

CREATE PARTITION FUNCTION pfMonPartition (datetime)

AS RANGE RIGHT FOR VALUES ( '1 jan 2009', '1 feb 2009', '1 mar 2009')

--2. create partition scheme

CREATE PARTITION SCHEME SalesPartitionScheme

AS PARTITION pfMonthlyPartition

TO ( [PRIMARY], [PRIMARY], [PartitionFG2]) -- to FileGroups

http://msdn.microsoft.com/en-us/library/ms179854.aspx

------------------------------

-- new table

------------------------------

-- 3. create table on the scheme using specific column as a key

CREATE TABLE dbo.[Sales](

[SalesID] [int] NOT NULL,
.........

[DateOfSale] [datetime] NOT NULL

) ON [SalesPartitionScheme](DateOfSale)

------------------------------

-- exists table
http://social.technet.microsoft.com/Forums/he-IL/sqlhe/thread/e4271d96-2aae-449b-9649-aa96cf87ed6e

------------------------------

-- 3. drop indexes and constraints on the key column

DROP INDEX [IX_Sales_DateOfSale] ON [dbo].[Sales] WITH ( ONLINE = OFF )

GO

ALTER TABLE [dbo].[Sales] DROP CONSTRAINT [PK_Sales]

GO

-- 4. re-create the indexes and the constraints of the key column on the scheme

CREATE CLUSTERED INDEX [IX_Sales_DateOfSale] ON [dbo].[Sales]

(

[DateOfSale] ASC

)WITH ( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF)

ON [SalesPartitionScheme]([DateOfSale])

GO

ALTER TABLE [dbo].[Sales] ADD  CONSTRAINT [PK_Sales] PRIMARY KEY NONCLUSTERED

(

[SalesOrderID] ASC,

[DateOfSale] DESC

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON SalesPartitionScheme([DateOfSale])