Table Partition in SQL Server

Table Partition will be used to split the table into multiple file groups so that we can reduce the lock escalation and we can improve query performance.

Suppose we have table like this

create table emp (id int identity ,            

                          name varchar(200),

                          Datejoined date default getdate(),

                          email varchar(100),

                          phonenumber bigint,

                          gender char(1),

                          alternatecontactnumber bigint )

In order to create partition table the database must be divided into
different file groups. We can check the current file groups in the database using the below query

       select * from sys.filegroups

In order to create partition the table first we have to add filegroups to the database using the below query.

ALTER DATABASE  PartitionDemo ADD FILEGROUP [Secondary]

GO

ALTER DATABASE PartitionDemo ADD FILEGROUP [Tretiry]       

GO 

We have created the file groups successfully.the second step in creating table partition is to create partition scheme.

CREATE PARTITION FUNCTION [partfunction](int) AS RANGE LEFT FOR VALUES (N'1000','2000')  

you can find the partition functions from the below query

select * from sys.partition_functions 


Similarly you can view the partition function ranges using the below query

select * from sys.partition_range_values

The partition function will use the boundaries with LEFT or RIGHT.

LEFT function will use the LEFT boundary.

1st partition -- < =1000 
2nd partition -- > 1000 and <=2000 

RIGHT function will use the RIGHT boundary

1st partition -- <1000 font="">
2nd partition -- > = 1000 and < 2000


The next step in creating the table partition is to create partition scheme using the partition function.

we can use the partition scheme using the following command

CREATE PARTITION SCHEME [PartScheme] AS PARTITION [partfunction] TO ([PRIMARY], [Secondary],[Tretiry]) 

using the below query we can find the partition schemes using the below query

select * from sys.partition_schemes

Now you can see the we have created partition schema as shown in the above picture.

Next we have to create a clustered index on the emp table using the partition scheme.

CREATE UNIQUE CLUSTERED INDEX [IXUC_PartScheme] ON [dbo].[emp]

(

      [id]

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartScheme]([id])

you can able see this partitions using the below query

select * from sys.partitions where OBJECT_NAME(object_id)='emp'

For new table you can able to assign the existing partition scheme using 

CREATE TABLE newEmp (ID int IDENTITY PRIMARY KEY, name varchar(100))

    ON [PartScheme] (ID)

 Based on the above picture you will know that you can use the partition scheme for as many tables as possible if the data type on partition scheme will match.

So we have successfully created table partition.


Now  we will load the data into partitioned table emp.

Execute the below query for inserting test data into partition table.

insert into dbo.emp (name) values ('testdata')

GO 1000 

We have inserted the 1000 rows using loop (GO statement).Now we will verify the number of rows in partition table emp.

We have inserted 1000 rows successfully. LEFT partition uses ranges <= 1000. so next row should be inserted into secondary filegroup (second partition). we will execute the statement.

insert into dbo.emp (name) values ('testdata')

Exception has been raised Msg 622, Level 16, State 3, Line 1
The filegroup "Secondary" has no files assigned to it. Tables, indexes, text columns, ntext columns, 
and image columns cannot be populated on this filegroup until a file is added.

we are getting the above error. because we did not add file to the secondary filegroup. we will the files to files group using the below query.

-- Add data file to [[Secondary]]

ALTER DATABASE PartitionDemo

ADD FILE (NAME = PartitionDemo1,FILENAME = 'D:\TestDB1.ndf')

TO FILEGROUP [Secondary]

GO

-- Add data file to [Tretiry]

ALTER DATABASE PartitionDemo

ADD FILE (NAME = PartitionDemo,FILENAME = 'D:\TestDB2.ndf')

TO FILEGROUP [Tretiry]

Once we have add files to filegroup we can able to insert the rows into secondary partition.

insert into emp (name) values ('1001row')

Data has been inserted successfully . new row has been inserted into second partition.

SELECT $PARTITION.[partfunction](1000) 'Partion number',

$PARTITION.[partfunction](2000) 'Partion number',

$PARTITION.[partfunction](1500) 'Partion number'

Using the  $PARTITION function we can verify the range belongs to which partition.

We can Merge the partition using the below query.

ALTER PARTITION FUNCTION [partfunction] ()

MERGE RANGE (1000); 

partition Now you can verify that there is only two partitions

We can split partition using the split function

ALTER PARTITION FUNCTION [partfunction] ()

SPLIT RANGE (1000);

Exception has been thrown "Msg 7707, Level 16, State 1, Line 1
The associated partition function 'partfunction' generates more partitions 
than there are file groups mentioned in the scheme 'PartScheme'.

The above error shows that two partitions are associated with two filegroups but the statement given in query is trying to split into one more partition but there is no filegroup associated with third partition. In order to split first we will associate the filegroup for third partition.

ALTER PARTITION SCHEME [PartScheme]

NEXT USED [Tretiry]; 

Now we can able to split the partition as shown below