Partition in SQL Server

Hi All,

I would like to share my knowledge on partitioning in SQL Server,

You can follow me here if you like this post.

Creating table partition on Varchar Column(YEAR MONTH wise)

1. Create file groups equivalent to the ranges of months we have in column YEARMONTH_SNAP(Example column name).       (See below screen shot)

    For Example, If we are inserting data monthly wise and you have 12 months of total data then create 13 file groups.

    Note: We have to create one extra file group than range values we have in column

2. Create Partition function with all the ranges of month we have. It will map all the column values to     respective partitions. Make sure  to same datatype in partition function as partition column data type, if you are partitioning on Int column then mention int() data type in PARTITION FUNCTION.

         

    CREATE PARTITION FUNCTION [PF_YM2] (varchar(7)) AS RANGE RIGHT FOR VALUES 

  (N'2016-11', N'2016-12', N'2017-01', N'2017-02', N'2017-03', N'2017-04', N'2017-              05',  N'2017-06', N'2017-07', N'2017-08', N'2017-09')

Note: The Partition function will keep the above range values in respective partitions using below                   logic.

Example:

        CREATE PARTITION FUNCTION PF_YM2 (Varchar (7)) 

        AS RANGE RIGHT FOR VALUES (1, 100, 1000); 

3. Create Partition Scheme using the partition function and file groups, it will map all the partitions         to respective file groups. 

      CREATE PARTITION SCHEME [PS_YM2] AS PARTITION [PF_YM2] TO ([FG1], [FG2], [FG3], [FG4],       [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11], [FG12])

Note: We have to add extra file group in partition scheme than the partition function range. If we insert more date range values in future it will use this additional file group, it means right now we have 12 partitions to store 12 months of data in case in future if you insert data for 13th month then that data will store in this additional file group.

Example: If we have 11 ranges in partition function we need to create partition scheme using 12 file                    groups.

4. Now create cluster index on partitioned column using partition scheme, it will map all the range values to particular partition

CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_YM] ON [dbo].[stgp_dcsinvb_cur_monthly]

(

       [YEARMONTH_SNAP]

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS_YM2]                  ([YEARMONTH_SNAP])

Validation:

       We can validate the partitions and mapped data on each partition using below script.

       /*Table Name, Index Name, Partition Number, Rows Partitioned*/

SELECT o.name objectname,i.name indexname, [partition_id], partition_number, [rows]

FROM sys.partitions p

INNER JOIN sys.objects o ON o.object_id=p.object_id

INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id

WHERE o.name = 'stgp_dcsinvb_cur_monthly'