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