Table Partition in SQL Server

We apply partition to table or database for easy management and faster access data and performance.

We can make partition of a table in Inside single Database. In case of multiple Database, then we called it as distributed partition view.

To avoid table partition, we can create multiple local views (LPV- Local partition views) of a table based on various range.

To do the table partition, we have decide the Partition Key.

•      The Partition Key is responsible for data distribution across your nodes.
•      The Clustering Key or Primary Key is responsible for data sorting within the partition.

For example If a table contain data’s form 2009 to till (2016). We want to make table partition by each year data.

• Number of Partition: 8
• Number of Boundary: 7

How to Create Table Partition

Step1: Create the file groups in the database.

Step2: Assign the files to file groups.

Step3: Create a Partition function for the table based on number of boundary.

Step4: Create a Partition schemes for the table based on Partition function  .

Step5: Create table based on Partition schema.

Practical Implementation:

1. Create a database named “Test_DB” on the SQL Server.
2. Go to Properties and click on file groups. Now add as many file group you want. Here I          have created 4 new file groups along with Primary File group.

3. Create new files and assign those file to File groups.

4. Now create a partition function with 3 boundary points.

     CREATE PARTITION FUNCTION Sales_Partition_func (int) as

    range right for values (2014,2015,2016) 

5. Create Partition Schemes based on partition Function.

     CREATE PARTITION SCHEME Sales_Partition_Schem AS

PARTITION Sales_Partition_func TO

(

[Data_2014],

[Data_2015],

[Data_2016],

[Data_Others])

6. Create Partition table. (Here you have to remember the partition column should be under     primary or unique key). 

     Create table Sales (

   Sales_Id Int Identity (1,1),

   Sales_Man Varchar(100),

   Sales_Date date,

   Sales_Amount Int,

   Sales_Year Int

   Primary key (Sales_Id,Sales_Year))

   ON [Sales_Partition_Schem] (Sales_Year) 

 Insert some values into sales table.

 Insert into Sales values ('Pritam Das','2013-06-13',100,2013)

 Insert into Sales values ('Pritam Das','2014-06-14',300,2014)

 Insert into Sales values ('Pritam Das','2014-08-17',600,2014)

 Insert into Sales values ('Pritam Das','2015-06-15',200,2015)

 Insert into Sales values ('Pritam Das','2015-08-18',500,2015)

 Insert into Sales values ('Pritam Das','2016-06-16',100,2016)

 Insert into Sales values ('Pritam Das','2016-08-14',300,2016)

 Insert into Sales values ('Pritam Das','2017-06-17',100,2017)

After Inserting values to sales table, Now is the time to Check for partition.

Select $PARTITION.[Sales_Partition_func](Sales_Year) as P_no,* from Sales

Here we have used Partition function and pass Partition Key (Sales_Year).

Output:

There are many more sql Sys table is there to check partition of table.

To get the Row count of particular partition you can run below query by change your partition table name.

Select * from sys.partitions where Object_id in (Select object_id from sys.objects where name='Sales')

Other Partition related SQL Queries.

1. To get the data of particular partition.
       
    Select * from Sales where $PARTITION.[Sales_Partition_func](Sales_Year)=4 

1   2. To get the Partition function existing in particular Database and each function details.               Select * from sys.partition_functions

     3. see partition range values and Boundary in a particular function.

Select * from sys.Partition_range_values where function_id in (select    function_id from sys.partition_functions where name='Sales_Partition_func' )

SELECT * FROM SYS.PARTITION_SCHEMES

SELECT * FROM SYS.DATA_SPACES

SELECT * FROM SYS.DESTINATION_DATA_SPACES

SELECT * FROM SYS.FILEGROUPS