Partitioning Table in SQL Server:

Partitioning Table in SQL Server:

 When your database has large tables. Then partitioning will be beneficial in terms of performance, scalability and maintainability. Sql Server partitioning allows to spread data on various physical disks leveraging the concurrent performance of those disks to optimize query performance. 

To use partitioning we create a database with multiple file groups. Here is an example with three file groups. The first is Primary Data file group. Others are Secondary file group. Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive.

Create database

CREATE DATABASE partioningDB

ON PRIMARY

(

NAME='PRIMARY',

FILENAME=

'C:\FG1\fg1.mdf',--Priamry data file

SIZE=6,

MAXSIZE=600,

FILEGROWTH=1

),

FILEGROUP fg2

(NAME = 'fg2',

FILENAME =

'D:\fg2\fg2.ndf',--ndf file is secondary data files make up all the data files, other than the primary data file

SIZE = 2,

MAXSIZE=100,

FILEGROWTH=1 )

,

FILEGROUP fg3

(NAME = 'fg3',

FILENAME =

'D:\fg3\fg3.ndf',--ndf file is secondary data files make up all the data files, other than the primary data file

SIZE = 2,

MAXSIZE=100,

FILEGROWTH=1 );

GO

Create Partition Function

Now we need to create a partition range function with following syntax.

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] )

[ ; ]

Ex.

CREATE PARTITION FUNCTION func_partition (int)

 AS RANGE RIGHT

 FOR VALUES (10,50)

FOR VALUES (10,50) defines Boundaries. These boundaries define three partitions. The first contains all values less than 10. The second contains values between 10 and 49. Third contains rest of values.

Now create a partition scheme

You need to create a scheme that defines where you want to partition data. Syntax to create scheme is

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )

[ ; ]

Ex.

CREATE PARTITION SCHEME scheme_partition

 AS PARTITION func_partition

 TO ([PRIMARY], fg2, fg3)

Partition is created on primary and fg2, and fg3 file groups.

Now to partitioning a table you need to create a table or alter table

Table Creation with partition

CREATE TABLE Person (fname nvarchar(40), lname nvarchar(40), [uid] int)

 ON scheme_partition ([uid])

 Partitioning on Existing Table

ALTER TABLE Person ADD CONSTRAINT [PK_uid] PRIMARY KEY CLUSTERED

(

[uid] ASC

)

WITH

(     PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

)

      ON scheme_partition([uid])

GO

Now partitioning of table has been done. To test insert some data in table

insert into Person values ('A','a',1)

insert into Person values ('B','b',2)

insert into Person values ('C','c',3)

insert into Person values ('D','d',23)

insert into Person values ('E','e',24)

insert into Person values ('F','f',25)

insert into Person values ('G','g',10)

insert into Person values ('H','h',60)

insert into Person values ('I','i',58)

insert into Person values ('J','j',54)

insert into Person values ('K','k',55)

Now to verify your data run this query

SELECT *

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Person';

Result:

See first partition have 3 rows means UID<10. As (1, 2, and 3)

Then Second contains 4 rows UID<50. (23, 24, 25, 10)

Then Third contains 4 rows UID>=50 as (55, 54, 58, 60)

Fetch Data from Table

Select *from Person