SQL Server : How to Create Table Partitioning Dynamically and Step by step with an example

In the Table partitioning process the first step we need to create is Partition function . A partition function is used to map the rows of a table into specific partitions (now have up to 15,000 partitions per table in SQL Server 2012, versus 1,000 per table in SQL 2005 & 2008).

Step 1  : Partition Function

===============

CREATE PARTITION FUNCTION M2MPartitionFunction (varchar(6)) AS RANGE RIGHT FOR VALUES ('201403', '201404', '201405', '201406', '201407', '201408', '201409', '201410', '201411', '201412');

we can achieve this by a dynamic script , we can make SP with the below script 

Declare @Startdate datetime2

Declare @Enddate datetime2

set @Startdate = '20140301'

set @Enddate = '20141201'

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION M2MPartitionFunction (varchar(6)) AS RANGE RIGHT FOR VALUES (';

DECLARE @i datetime2 = @Startdate  ;

WHILE @i < @Enddate

BEGIN

 

                SET @DatePartitionFunction += '''' + CAST(DATEPART(year, @i)*100 + DATEPART(month, @i)  AS varchar(6)) + '''' + N', ';

                SET @i = DATEADD(MM, 1, @i);

END

SET @DatePartitionFunction += '''' + CAST(DATEPART(year, @i)*100 + DATEPART(month, @i)  AS varchar(6)) + '''' + N');';

select @DatePartitionFunction;

GO

Step 2 : Create Partition Scheme .

Partiton scheme which allows us to map our database partitions created by the partition function to file groups. Again this can be as simple or complex as you would like it to. You can map all your partitions to one file group:

CREATE PARTITION SCHEME M2MPartitionScheme AS PARTITION M2MPartitionFunction TO ('FS_DATA_1', 'FS_DATA_2', 'FS_DATA_3', 'FS_DATA_1', 'FS_DATA_2', 'FS_DATA_3', 'FS_DATA_1', 'FS_DATA_2', 'FS_DATA_3', 'FS_DATA_1', 'FS_DATA_2');

It is better idea to keep different file group for different partition  if we only one File group  then

CREATE PARTITION SCHEME M2MPartitionScheme AS PARTITION M2MPartitionFunction All TO ('FS_DATA');

You can achieve this query dynamically by using the below query

DECLARE @DatePartitionScheme nvarchar(max) = N'CREATE PARTITION SCHEME M2MPartitionScheme AS PARTITION M2MPartitionFunction TO (';

DECLARE @i datetime2 = '20140301';

DECLARE @m int = 12

WHILE @i <= '20141201'

BEGIN

                SET @DatePartitionScheme += '''' + 'FS_DATA_' + CAST((@m % 3) + 1  AS varchar(2)) + '''' + N', ';

                SET @i = DATEADD(MM, 1, @i);

                SET @m = @m + 1          

END

SET @DatePartitionScheme += '''' + 'FS_DATA_' + CAST((@m % 3) + 1  AS varchar(2)) + '''' + N');';

select @DatePartitionScheme

Step 3 :

Create Clustered index  Using that Partition schema Or Create a table using partition schema

========================

CREATE CLUSTERED INDEX [ixBill_Period_ParsedUsage] ON M2M_Schema.ParsedUsage (  [Bill_Period] ASC )

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

ON [M2MPartitionScheme]([Bill_Period])

GO

OR you can create table  

-- Creation of table

CREATE TABLE [M2M_Schema].[ParsedUsage](

      [ID] [bigint] IDENTITY(1,1) NOT NULL,

      [Date_Parsed] [datetime] NULL,

      [File_Name] [varchar](80) NULL,

      [SubscriberID] [bigint] NULL,

      [MSISDN] [nvarchar](100) NULL,

      [ICCID] [nvarchar](100) NULL,

      [Rate_Class_ID] [smallint] NULL,

      [Rate_Class_Name] [nvarchar](100) NULL,

      [Amount] [decimal](15, 3) NULL,

      [Unit_Of_Measure] [varchar](50) NULL,

      [PLMN] [nvarchar](50) NULL,

      [Bill_period] [varchar](6) NULL,

PRIMARY KEY NONCLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FS_DATA]

) ON [M2MPartitionScheme]([Bill_Period])