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