Script to Partition in Existing Table in SQL Server

Partition in Existing Table in SQL Server.

Step1 : Create Multiple File Group Database.


CREATE DATABASE MULTIFILEGROUP
ON PRIMARY
(NAME = N'MFGTest_1', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_1.mdf', SIZE = 5124KB, FILEGROWTH = 10% ),
FILEGROUP [SECONDARY]
(NAME = N'MFGTest_2', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_2.ndf', SIZE = 1024KB, FILEGROWTH = 10% )
LOG ON
( NAME = N'MFGTest_Log', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_Log.ldf', SIZE = 1024KB, FILEGROWTH = 10% )

Step2 : Create Simple Table with out partition.


USE [MULTIFILEGROUP]
GO
--Create Simple Table.
CREATE TABLE [DBO].[Tblpartition_existingtable]
(
[Seq]           [INT] IDENTITY(1, 1) NOT NULL,
[Partitionname] [VARCHAR](20),
[Split_value]   [INT] NOT NULL,
CONSTRAINT [PK_TBLPARTITION_EXISTINGTABLE] PRIMARY KEY CLUSTERED ( [Seq]
ASC, [Split_value] ASC )
)

Step3 : Insert sample data into table with out partition.

--Insert Data into Table.
DECLARE @cnt INT
SET @cnt =0
WHILE ( @Cnt < 10000 )
  BEGIN
      IF ( @cnt < 5000 )
        INSERT INTO Tblpartition_existingtable
        VALUES     ('Primary', @cnt)
      ELSE
        INSERT INTO Tblpartition_existingtable
        VALUES     ('Secondary', @cnt)
      SET @cnt = @cnt + 2
  END

Currently we have created simple table without partition and now the step starts to create partition in existing table.

Step4 : Create partition function.

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION_EXISTINGTABLE (INT) AS RANGE RIGHT FOR
VALUES(5000)

Step5 : Create partition scheme.


--Create Parition Scheme
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION_EXISTINGTABLE AS PARTITION
FN_PARTITION_TBLPARTITION_EXISTINGTABLE TO([PRIMARY], [SECONDARY])

Step6 : Drop primary key if you clustered index based on primary key.


--Drop Primary Key (Here i have created primary key so i have to drop primary key)
ALTER TABLE Tblpartition_existingtable
DROP CONSTRAINT PK_TBLPARTITION_EXISTINGTABLE

Step7 : Drop clustered index if your index is independent of primary key.


--Drop and Recreate Clustered Index (It is optional, you can use if you have created clustered index without primary key)
DROP INDEX PK_TBLPARTITION_EXISTINGTABLE ON TBLPARTITION_EXISTINGTABLE

Step8 : Alter table and apply partition scheme.


--Alter Table to apply Partition Schema
ALTER TABLE Tblpartition_existingtable
ADD PRIMARY KEY (Seq, Split_value) ON SCH_PARTITION_TBLPARTITION_EXISTINGTABLE
(SPLIT_VALUE)

Step9 : Check row count by partion using below query.


SELECT T.Name             AS Table_name,
Ps.Name            AS Partition_scheme,
Pf.Name            AS Partition_function,
CASE
WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
ELSE 'LEFT'
END                AS Range_type,
P.Partition_number AS Partition_number,
P.Rows             AS Numberofrows
FROM   SYS.Tables T
JOIN SYS.Indexes I
ON T.Object_id = I.Object_id
JOIN SYS.Partition_schemes Ps
ON I.Data_space_id = Ps.Data_space_id
JOIN SYS.Partition_functions Pf
ON Ps.Function_id = Pf.Function_id
JOIN SYS.Partitions P
ON I.Object_id = P.Object_id
AND I.Index_id = P.Index_id
WHERE  I.Index_id < 2 --Filter for Clustered Index
ORDER  BY P.Partition_number

Regards,
Nirav Gajjar

Script to get Table Partition information

Script to get Table Partition information.

This script gives you Table Partition information with Partition Number, Scheme Name and Function Name.

SELECT T.Name  AS Table_Name,
       Ps.Name AS Partition_Scheme,
       Pf.Name AS Partition_Function,
       CASE
         WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
         ELSE 'LEFT'
       END  AS Range_type,
       P.Partition_number AS Partition_Number,
       P.Rows AS NumberOfRows
FROM   SYS.Tables T
       JOIN SYS.Indexes I
         ON T.Object_id = I.Object_id
       JOIN SYS.Partition_schemes Ps
         ON I.Data_space_id = Ps.Data_space_id
       JOIN SYS.Partition_functions Pf
         ON Ps.Function_id = Pf.Function_id
       JOIN SYS.Partitions P
         ON I.Object_id = P.Object_id
            AND I.Index_id = P.Index_id
WHERE  I.Index_id < 2 --Filter for Clustered Index
ORDER  BY P.Partition_number

TablePartition

Regards,

Nirav Gajjar