SQL Server 2012 - Partitioning an exisiting table

Question

We are in process of partitioning an existing table. Let me share a brief about the background:

Issue Background: Have got few guidelines from here: https://dba.stackexchange.com/questions/48011/how-to-partition-an-existing-non-partitioned-table

It says,

Step 1: first create a partition function and partition scheme
Steps 2 & 3 not applicable to my case
Step 4: If your table does not have a clustered index, then you can just create one on the right partition using the partition scheme.

Question:

I've done with Step1. While performing step 4, is it mandatory to create a clustered index. If so, Why?
I have a datekey column(INT datatype)of the table, which would have plenty of rows for one particular datekey(sample-20150825). I'm planning to choose the characters '201508' as the partition key and all records for that month should flow in that partition. Is this possible to proceed with ? If so, please help me with right directions.

Many Thanks. Lakshman.

It's not manditory. However, having a clustered index that is aligned with the partitioning function makes partition level operations much more efficient. For instance with partition switching. — Amir Pelled, Aug 25 '15 at 15:31
why not use a date type instead of Int for dateKey? Then you could partition on the first day of the month. see http://stackoverflow.com/questions/31924034/how-to-partition-a-table-by-month-both-year-month-and-create-monthly-parti/31941568#31941568 — Julien Vavasseur, Aug 26 '15 at 07:39
@JulienVavasseur: As mentioned it is an existing table that does not "allow" datatype changes. But thanks for the link which seems helpful in a different area. — Lakshmanan Chidambaram, Sep 02 '15 at 10:10

Julien Vavasseur · Accepted Answer · 2015-09-02T13:57:03.907

I would probably go with something like this. I supposed that you insert datakey in acsending order without going back or any update on it. In this example, I assumed that your oldest datekey is in January 2015.

Create a test table with no clustered index:

create table dbo.test(id int identity(0, 1) primary key nonclustered, datekey int, data nchar(2000))
go
insert into test(datekey, data) values 
(20150125, ''), (20150120, ''), (20150118, ''), (20150118, ''), (20150118, '')
, (20150205, ''), (20150215, ''), (20150215, ''), (20150215, ''), (20150215, '')
, (20150305, ''), (20150315, '')

Create Filegroups and files:

Alter Database [Test] Add Filegroup [Part_201501]
Alter Database [Test] Add Filegroup [Part_201502]
Alter Database [Test] Add Filegroup [Part_201503]
Alter Database [Test] Add FILE ( NAME = N'Part_201501', FILENAME = N'...\Part_201501.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201501]
Alter Database [Test] Add FILE ( NAME = N'Part_201502', FILENAME = N'...\Part_201502.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201502]
Alter Database [Test] Add FILE ( NAME = N'Part_201503', FILENAME = N'...\Part_201503.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201503]

Create function starting with everything before 20150201 (meaning 01-2015):

Create Partition Function DateKeyPartFunction (int)
as Range Right For Values (20150201, 20150301)

Note that I cannot partition by part of the datakey like 201501. This is why I partition by the first day of the following month. All datekey >= 20150201 and < 20150301 will be part of the Part_201502 partition.

Create Scheme:

Create Partition Scheme DateKeyPartScheme as Partition DateKeyPartFunction
To ([Part_201501], [Part_201502], [Part_201503])

Create a clustered index:

Create Clustered Index IDX_Part On dbo.Test(datekey) On DateKeyPartScheme(datekey);

If you have a clustered primary key. You have to replace it by a non clustered PK (+remove/add FKs). This won't change the types of your table.

Once you reach April, you only have to add a new Part_201504 filegroup and split the function on 20150401...

SQL Server 2012 - Partitioning an exisiting table

1 Answers1