Thursday 23 June 2016

What is Partition of a table and how to create a Partition in table to improve the performance of the Tables in SQL SERVER


In This post we are going to see what is partition and how to create a partition in SQL SERVER.

After index a table we can further optimize the performance of a table by partitioning the tables and indexes. Partitioning allows you to modify the data without affecting the integrity of the entire collection. Partition is the process of distributing the data of tables and indexes on separate filegroups.

       Need to perform the following task to create the partition
o   Create a Partition Function
o   Create File groups
o   Create a Partition Scheme
o   Create a Partition Table

-- Create a Partition Function [function takes one parameter as input]
CREATE PARTITION FUNCTION emp_partfunc(INT)
AS RANGE LEFT        --Left keyword specifies that vlaues one less than the specified
FOR VALUES(1980,1990,2000,2010)

-- Create a File group
RIGHT Click ON DATABASE
CLICK Properties AND SELECT FileGroups AND ADD four GROUP f1,f2,f3,f4

-- Create a Partition Scheme
CREATE PARTITION SCHEME emp_part_sch
AS PARTITION emp_partfunc
TO (f1,f2,f3,f4)

-- Create a partition table
CREATE TABLE employ(id INT IDENTITY(1,1), NAME VARCHAR(40),yearofjoin INT)
ON emp_part_sch(yearofjoin)




No comments:

Post a Comment