SQL Server table partitioning

Table partition is the new feature introduced in sql server 2005 to improve the performance of the sql server. This will help you to split large table into partitions; we can place these partitions either on the same drive or different drive/ Hard Disk.

Before going for the partitioning, identify the column that will participate in the process, it will map the rows of a table and index into partitions based on the column values specified.

Let’s go through with simple Partition to understand the SQL Server partitioning concept in sql server

1. We will create test database with the named Partitiondb

USE master

CREATE DATABASE Partitiondb
ON PRIMARY
(NAME=’ Partitiondb_Part1′,
FILENAME=
‘C:\SQL\Data\ Partitiondb.mdf’,
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP Partitiondb_fg
(NAME = ‘ Partitiondb2′,
FILENAME =
‘D:\SQL\Data2\ Partitiondb2.ndf’,
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 );

USE Partitiondb;

2. Now create Partition range Function with value known as boundary_value

CREATE PARTITION FUNCTION Partitiondb_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (5,10,15);

After executing above query, 4 partitions will get created in the database of the sql server.

3. Here, we will create partition scheme for partition function and attach that to filegroup

CREATE PARTITION SCHEME Partitiondb_PartitionScheme
AS PARTITION Partitiondb_PartitionRange
TO ([PRIMARY], Partitiondb2);

If you have created only one filegroup with file and trying to create 4 partitions, it’s not possible when you execute above statement it will through below error.

Msg 7707, Level 16, State 1, Line 2

The associated partition function ‘Partitiondb_PartitionRange ‘ generates more partitions than there are file groups mentioned in the scheme ‘ Partitiondb_PartitionScheme’.

 You have to add two more file in filegroup to create partition

 ALTER DATABASE [PartitionDB]  Add filegroup Partitiondb_fg2

 ALTER DATABASE [PartitionDB] ADD FILE

( NAME = N’PartitionDB_part2′, FILENAME = ‘D:\SQL\Data2\ Partitiondb_part2.ndf’ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

to

 FILEGROUP Partitiondb_fg2

 

ALTER DATABASE [PartitionDB] Add filegroup Partitiondb_fg3

 ALTER DATABASE [PartitionDB]

ADD FILE

( NAME = N’PartitionDB_part3′, FILENAME = ‘D:\SQL\Data2\ Partitiondb_part3.ndf’, SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

to

 FILEGROUP Partitiondb_fg3

 

4. Lets create test table with partition key (ie ID) and attach the Partition scheme which we have created in the earlier step 3. Also pass the partition key to partition scheme as a parameter

CREATE TABLE Stdetail
(ST_ID INT IDENTITY(1,1),
Date DATETIME
)
ON Partitiondb_PartitionScheme (ID);

5. We have created unique clustered Index on Stdetail table as per recommendation

CREATE UNIQUE CLUSTERED INDEX unique_Clust_Stdetail
ON Stdetail (ID)
ON Partitiondb_PartitionScheme (ID);

6. Insert Data into Stdetail (Partitioned) Table, This query will insert 20 rows in the table with unique value Partition key column.

INSERT INTO Stdetail (Date)
VALUES (GETDATE());
GO 20

As per partition range function, if value is <5 than that will get inserted in partition 1.
If value is >= 5 and < 10 than it will get inserted in partition 2.
If value is >= 10 and < 15 than it will go in partition 3.
If value is >= 15 than that will go in partition 4 (i.e primary filegroup).

7. Check the data from Stdetail after executing below query. It will give 2o rows in the table.

SELECT * FROM Stdetail;

8. We can verify Rows Inserted in Partitions by executing below query.

SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)= ‘Stdetail’;

 

Leave a Reply

  

  

  


+ 8 = 14