Table partitioning

What is partitioning.

To start with partition is the feature provided by SQL server in which very large tables are split between multiple files and file-groups for ease of access and faster retrieval/ update purposes.
Consider a table which consists of millions of records. When we insert all the records in one table, in one file; retrieval or update time for any of the record even if it involves index seek is considerably high. A better option would be to split the table based on certain column values. Let the data be spread across multiple file-groups.

When a search condition is encountered comprising on that column value SQL server will now have to look at smaller chunks of data of any one of those multiple files, thus resulting faster retrieval and execution time.

How to create partitions

Step 1: Create File Groups

Obviously we shall first begin with creating different file groups and allocating files to each group. This can be done from the management studio as follows under the Database Property-> Filegroups Section 

Step 2: Assign files to these groups

Next step is creating files and assigning them to these groups. We may assign multiple files to one file group. SQL server will use proportional fill algorithm when data is inserted into these files.Below image depicts one file assigned to each groups.

If you love coding below is the T-SQL for the same

USE [master]

GO

ALTER DATABASE [test] ADD FILEGROUP [F1]

GO

ALTER DATABASE [test] ADD FILE ( NAME = N'Test1', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\Test1.ndf' , SIZE = 5120KB , FILEGROWTH = 10%) TO FILEGROUP [F1]

GO

ALTER DATABASE [test] ADD FILEGROUP [F2]

GO

ALTER DATABASE [test] ADD FILE ( NAME = N'Test2', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\Test2.ndf' , SIZE = 5120KB , FILEGROWTH = 10%) TO FILEGROUP [F2]

GO

ALTER DATABASE [test] ADD FILEGROUP [F3]

GO

ALTER DATABASE [test] ADD FILE ( NAME = N'Test3', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\Test3.ndf' , SIZE = 5120KB , FILEGROWTH = 10%) TO FILEGROUP [F3]

GO

ALTER DATABASE [test] ADD FILEGROUP [F4]

GO

ALTER DATABASE [test] ADD FILE ( NAME = N'Test4', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\Test4.ndf' , SIZE = 5120KB , FILEGROWTH = 10%) TO FILEGROUP [F4]

GO

Step 3: Deciding the column for partition

Next step is for us to decide the criteria on which data will be partitioned. Ideally tables that contain historic data are partitioned. In our example we will be implementing this for similar OrderDetail Table. Data will be partitioned based on OrderDate. Structure is as follows.

Step 4: Creating Partition Function

First we shall create decide the ranges which is defined by the partition function
In our case the function will be as below


CREATE PARTITION FUNCTION [OrderDatesMonthly] (datetime)

AS RANGE LEFT FOR VALUES ('20150401','20150701','20151001');

Since we have specified 11 values our total number of partitions will be 3+1 =4.

Partition	Values
1	date<=20150401	<= 1st April 2015
2	date<=20150701	<=  1st July 2015
3	date<=20151001	<= 1st October 2015
4	date>20151201	> 1st October 2015

I’ve used the RANGE LEFT option here. One may also choose to opt for RANGE RIGHT. For example

CREATE PARTITION FUNCTION [OrderDatesMonthly] (datetime)

AS RANGE RIGHT FOR VALUES ('20150330','20150630','20150930');

Step 5 : Creating Partition Scheme

Now that we’ve split the range lets map it to different filegroups. Ranges are split with functions. Split ranges are mapped with schemes. In our case the scheme will be

CREATE PARTITION SCHEME [OrderDatesMonthlyScheme] 

AS PARTITION [OrderDatesMonthly] TO (F1, F2, F3, F4);

Step 6: Breaking the table

Our range is set our file-groups are mapped so now let’s break our tables into smaller manageable chunks.
     

CREATE TABLE dbo.[OrderTable](
[OrderId] [nchar](10) NOT NULL,

[OrderDate] [smalldatetime] NOT NULL,

 CONSTRAINT [PK_OrderTable] PRIMARY KEY CLUSTERED

(

       [OrderId] ASC

)

) ON OrderDatesMonthlyScheme(OrderDate)

GO

In just 6 steps we saw how too break large tables into partition tables.
In the coming blogs we will learn how to move data between such tables.