Partition a Table for better performance in Sql Server 2005 / 2008

In earlier versions we didn't have partitioning a table option to distribute the data across multiple file groups. This was introduced with Sql server 2005 including other valuable features. Partitioning can be performed with help of Partition Function, Partition Scheme.

Partition is nothing like distributing the data across multiple file groups, as we all know if we share the data into multiple drives automatically performance of the database will be increased. Let assume we have a big table comprises million of records, when fetching the data it took too long time. So, for increasing the performance of the table we can implement Partitioning concept and achieve the better performance.

HOW TO PARTITION A TABLE :
Requirement : A table is to be defined with multiple columns, out of which we need to fetch the data based on column "Name". If you select data based on Name there would be many records with different names comprising letters from A-Z. So by using following method, the data can be distributed into different file groups.

Step 1 : As a first step you need to create a Partition function :
CREATE PARTITION FUNCTION NameFunc (nvarchar(30))
AS RANGE RIGHT FOR VALUES ('H', 'N')


Step 2 : Need to create partition scheme based on the partition function :

CREATE PARTITION SCHEME NameScheme
AS PARTITION NameFunc TO (fg1, fg2, fg3)


Step 3 : Create a table based on Function and Scheme

CREATE TABLE dbo.Employees
(EmpID int, Name nvarchar(30))
ON NameScheme (Name)



Explanation :
In the first step 1, 'H', 'N' values defines
A-H first group,
H-N second group,
N-Z Third group

the above 3 groups will be stored on fg1, fg2 and fg3 file groups respectively.