Partition Table in SQL Server

Partition Table in SQL Server

To create table partition your database must need Multple file group database to apply Table partition.

Step1 : Create Multiple Filegroup 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 Partition Function to define partition block .

here, i have created two partition block

See Values(10) means first partition contains 1-10 and second partition contains > 10.

you can create number of partition block same as number of file group in your database.

you can create multiple block as below.
VALUES(10,20,30,40)– it will create 5 blocks 1-10, 11-20, 21-30, 31-40, >40

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION (INT) AS RANGE RIGHT FOR
VALUES(10)

Step3 : Create partition Schema.

--Create Parition Schema
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION AS PARTITION
FN_PARTITION_TBLPARTITION TO([PRIMARY], [SECONDARY])

Step4 : Create Parition Table.

CREATE TABLE Tblpartition
  (
     Seq         INT IDENTITY(1, 1) NOT NULL,
     Fisrt_name  VARCHAR(20),
     Last_name   VARCHAR(20),
     Split_value INT NOT NULL
  )
ON SCH_PARTITION_TBLPARTITION(SPLIT_VALUE)

ALTER TABLE Tblpartition
  ADD CONSTRAINT PK_TBLPARTITION PRIMARY KEY (Seq, Split_value)

Step5 : Insert sample data in partition table.

INSERT INTO Tblpartition
SELECT 'Nirav', 'Gajjar', 1
UNION ALL
SELECT 'Smith', 'Mack', 2
UNION ALL
SELECT 'Jack', 'Samson', 3
UNION ALL
SELECT 'Smith', 'Raka', 10
UNION ALL
SELECT 'Hen', 'Vaith', 11

Step6 : Script to get Paritition Table Information.

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