Table Partitioning

• Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems. 
• The feature is only available for enterprise and developer edition. 
• For other editions you can get a similar functionality with a partitioned view

STEP : 1 Create Database with multiple FileGroups and multiple Files

Under respective path mentioned, following files would be created

• PartitionDemo.mdf
• PartitionDemo_log.ldf
• PartitionDemoFile1.ndf
• PartitionDemoFile2.ndf

It would be advisable if you create Primary FileGroup Files and NewFileGroup Files on separate, that will boost the performance.

STEP : 2 Create Partition Range Function

USE PartitionDemo;GO--- Step 2 : Create Partition Range FunctionCREATE PARTITION FUNCTION PartitionDemo_Range (INT)AS RANGE LEFT FOR
VALUES (10,20,30);GO
Basically this will create 4 partitions

• Partition 1 ------- <= 10
• Partition 2 ------- >10 and <= 20
• Partition 3 ------- >20 and <= 30
• Partition 4 ------- >30

Here LEFT keyword specifies that boundary values would be considered in left bucket. so 20 would come under Partition2 bucket rather than Partition2 bucket

STEP : 3 Create Partition Scheme


USE PartitionDemo;GO--- Step 3 : Attach Partition Scheme to FileGroupsCREATE PARTITION SCHEME PartitionDemo_SchemeAS PARTITION PartitionDemo_RangeTO ([PRIMARY], [FG1], [FG2], [FG2]);GO
This is a process of assigning partitions to different filegroups.
This says that 

• Partition 1 --------- Primary FileGroup
• Partition 2 --------- FG1  FileGroup
• Partition 3,4 ------- FG2  FileGroup

STEP : 4 Create Table with Partition Key and Partition Scheme


USE PartitionDemo;GO--- Step 4 : Create Table with Partition Key and Partition SchemeCREATE TABLE TestTable(ID INT NOT NULL,Date DATETIME)ON PartitionDemo_Scheme (ID);GO

So this says that table has been linked with scheme and "ID" column will decide how record would be distributed among different partitions


STEP : 5 Create Index on Partitioned Table


USE PartitionDemo;GO--- Step 5 : Create Index on Partitioned TableCREATE UNIQUE CLUSTERED INDEX IX_TestTableON TestTable(ID)ON PartitionDemo_Scheme (ID);GO
This is highly recommended to improve performance


STEP : 6 Insert Data into Partitioned Table


USE PartitionDemo;GO--- Step 6 : Insert Data in Partitioned TableINSERT INTO TestTable (ID, Date) -- Inserted in Partition 1VALUES (1,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1VALUES (10,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2VALUES (11,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2VALUES (20,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3VALUES (21,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3VALUES (30,GETDATE());INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4VALUES (31,GETDATE());GO
STEP : 7 Verify rows against different partitions


USE PartitionDemo;GO--- Step 8 : Verify Rows Inserted in PartitionsSELECT *FROM sys.partitionsWHERE OBJECT_NAME(OBJECT_ID)='TestTable';GO
SOMETHING EXTRA

• We can add/remove partition using SPLIT and MERGE keywords
• If the index contains the partitioning column then the index is referred to as being 'aligned' with the table
• Partitioning column can be number, date, string datatype
• We can consider Calculated column as Partition key also