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