How to create a partition for an existing table and index?

Take the following steps to create a partition for an existing table and index:

1. Create File Group:

To create a filegroup named 2003Q3 for the AdventureWorks database, use ALTER DATABASE:

ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]

Once a filegroup exists then you use ALTER DATABASE to add files to the filegroup.

ALTER DATABASE AdventureWorks
ADD FILE
	(NAME = N'2003Q3',
	FILENAME = N'C:\AdventureWorks\2003Q3.ndf',
	SIZE = 5MB,
	MAXSIZE = 100MB,
	FILEGROWTH = 5MB)
	TO FILEGROUP [2003Q3]

2. Create a partition function:

A partition function can be created by using the CREATE PARTITION FUNCTION STATEMENT. An example is given below:

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES (
	'20000930 23:59:59.997',
	'20001231 23:59:59.997',
	'20010331 23:59:59.997',
	'20010630 23:59:59.997')

For RANGE LEFT

1st partition is all data <= '20000930 23:59:59.997’

2nd partition is all data >  '20000930 23:59:59.997’ and <= '20001231 23:59:59.997'

3rd partition is all data > '20001231 23:59:59.997' and <= '20010331 23:59:59.997'

4th partition is all data > '20010331 23:59:59.997' and <= '20010630 23:59:59.997'

5th partition is all data > '20010630 23:59:59.997'

For RANGGE RIGHT

1st partition is all data < '20000930 23:59:59.997’

2nd partition is all data >=  '20000930 23:59:59.997’ and < '20001231 23:59:59.997'

3rd partition is all data >= '20001231 23:59:59.997' and < '20010331 23:59:59.997'

4th partition is all data >= '20010331 23:59:59.997' and < '20010630 23:59:59.997'

5th partition is all data >= '20010630 23:59:59.997'

3. Create a partition scheme:

A partition scheme can be created by using the CREATE PARTITION SCHEME statement. An example is given below:

CREATE PARTITION SCHEME OrderDateRangePS
AS PARTITION OrderDateRangePFN
TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])

4. Drop the existing clustered index:

If in your table has clustered index (default for primary keys), you must drop it and recreate nonclustered index with primary keys of table. The clustered index can be dropped by using the DROP INDEX statement. The statement is as follows:

ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [PK_Orders]

ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED 
(
	[OrderID] ASC
)

5. Create the clustered index on the partition scheme:

The index can be created on a partitioned scheme as follows:

CREATE CLUSTERED INDEX [PartitionIndex_on_OrderDateRangePS] ON [dbo].[Orders] 
(
	[OrderDate]
)
	WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) 
	ON [OrderDateRangePS]([OrderDate])

The table is automatically partitioned according to the partition scheme of the clustered index.