Horizontal partition of MS SQL server database table

Introduction

One of my friends asks me a question that, he has a large table and the table is well indexed. When he use the query (SELECT statement) he got the INDEX Seek (Not SCAN) but the problem is the response time of the query is so slow.

What u thinks? There is N number of reason related to it. Bust most common is that the table has huge data and it needs to be partitioned.

So all of you understand that, in this article we are going to discuss related to table partition.

Some general facts related to Table Partition

It is the horizontally partition the table. The technology came from MS SQL 2005 onwards. It is an MS SQL Server Enterprise edition feature. But we can test it in Developer Edition also. To get the proper effects of table partitioning we need multiple storage location (physical storage).

It allows us to different database files, which can be located on different disks to improve performance.

What is in Before MS SQL 2005

Before MS SQL 2005 we do not have the facility to make Horizontal partition of MS SQL server database table. But we can create the separate table of different file group of Database and create a VIEW by using UNION.

 How to make Horizontal partition of table

Step-1 [ Create the Database with Different File group ]

CREATE DATABASE [Employee_DB] ON

PRIMARY

 (

   NAME = N'Employee_DB',

   FILENAME = N'C:\EmployeeData\Employee_DB.mdf' ,

   SIZE = 3072KB , FILEGROWTH = 1024KB

 ),

FILEGROUP [EmployeeDBSecond]

(

  NAME = N'Employee_DB_Second',

  FILENAME = N'C:\EmployeeData\PEmployee_DB_Second.ndf' ,

  SIZE = 3072KB , FILEGROWTH = 1024KB

)

LOG ON

(

  NAME = N'Employee_DB_log',

 FILENAME = N'C:\EmployeeData\Employee_DB_log.ldf' ,

 SIZE = 9216KB , FILEGROWTH = 10%

)

GO

Here

Employee_DB.mdf	MS SQL Server Primary Database File
PEmployee_DB_Second.ndf	MS SQL Server Secondary Database File
Employee_DB_log.ldf	MS SQL Server Transaction Log File

“Please note that in this example we are using a single storage to make the primary and secondary file group. It is better if we take the different physical storage for primary and secondary file group storage to increate the IO. So increase the performance”.

Step-2 [ Create Partition Function ]

The partition function defines that how to separate the data. The function is not related to any specified table that determines how the data split occurs.

In our example we take Sales Order information where current year records stores in Primary file group and all the older data must store in secondary file group.

CREATE PARTITION FUNCTION fnEmpDBPartFunc (DATE)

AS RANGE LEFT

FOR VALUES ('2013-12-31')

1	>   2013
2	<=  2013

Step-3 [ Creating Partition Schema ]

Here the Partition function is created, so the SQL Server knows that how to segregate the data but doesn’t know that where to put the partitioned data. This is done by Partition Schema and the Partition schema is linked with Partition function.

CREATE PARTITION SCHEME EmpDBParttScheme

AS PARTITION fnEmpDBPartFunc

TO ([EmployeeDBSecond], [PRIMARY])

File Group – PRIMARY	>   2013
File Group - EmployeeDBSecond	<=  2013

Strep-4 [ Creating Partition Table ]

Now we create Table on partition schema.

CREATE TABLE Table_SalesOrders

(

      OrderID     INT,

      CustName    VARCHAR(50),

      OrderDate   DATE

)

ON EmpDBParttScheme (OrderDate)

Step- 5 [ Inserting Records ]

-- Will go to [PRIMARY] File Group

INSERT INTO Table_SalesOrders

       (OrderID, CustName, OrderDate)

VALUES (1, 'Joydeep Das', '2014-01-10')

-- Will go to [EmployeeDBSecond] File Group

INSERT INTO Table_SalesOrders

       (OrderID, CustName, OrderDate)

VALUES (2, 'Manayan Chaturvedhi', '2013-06-22')

Step-6 [ Checking the Partition ]

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

partition_id         object_id   partition_number rows

72057594038910976    5575058     1                1

72057594038976512    5575058     2                1

Sterp-7 [ Creating CLUSTERED Index ]

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_SalesOrders]

(

   [OrderID] ASC

) ON EmpDBParttScheme (OrderDate)

Hope you like it.

Posted by: MR. JOYDEEP DAS