Wednesday, December 29, 2010

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

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 (IDDate-- Inserted in Partition 2VALUES (20,GETDATE());INSERT INTO TestTable (IDDate-- Inserted in Partition 3VALUES (21,GETDATE());INSERT INTO TestTable (IDDate-- Inserted in Partition 3VALUES (30,GETDATE());INSERT INTO TestTable (IDDate-- 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

  • 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