Partitioning in SQL Server


One of the efficient ways of improving performance is partitioning the table that allows storing data in more than one physical file. Consider a scenario where a table contains 1 million rows, when a query is executed against that it might have to scan huge #'s of rows before it returns the result. Separating data in different filegroups will help scanning all the physical files at a same time
.

Search is simultaneous in all filegroup

One database can have 32,767 files. If a table is not partitioned all data will go in PRIMARY Files, when a table is partitioned data goes in its defined partition, if it falls outside the range then it ends up in PRIMARY file.

So, let's see how we can create a range partition. In this scenario, consider a Database named "Partition" having table named "RangePart" which stores data month by month . We want to partition Rangepart in 12 parts where each month's data ends up in different partition. So first thing is we create different partition on database as shown below.

  1. Right Click in Database and select Properties
  2. From Left panel, select Files

3. Click on Add, a new row will be created and I am naming it as m1 and under Filegroup column click new filegroup

  1.  

4. Small window will open and you can give a name, to keep things simple I am naming it as m1 as well and click OK

5. In the same way, create 12 filegroups with further names as m2, m3,m4,m5 and so on till m12 as shown below

6. Click Ok. Now we need to create partition function as below:

CREATE PARTITION FUNCTION [partfunction](int) AS RANGE LEFT FOR VALUES (1, 2, 3, 4,5,6,7,8,9,10,11,12)

GO

 7. We will be creating a partition scheme to map the file names that we created above

CREATE PARTITION SCHEME [partfunctionScheme] AS PARTITION [partfunction]

TO ([m1], [m2], [m3], [m4],[m5],[m6],[m7],[m8],[m9],[m10],[m11],[m12], [PRIMARY])

GO

 8. Now we create our table and make sure that it reference partition scheme as below.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Rangepart](

 

    

    [EmployeeID] [nvarchar](50) NULL,

    [Name] [nvarchar](300) NULL,

    [Department] [nvarchar](50) NULL,

    [UploadDate] [datetime] NOT NULL,

    [Month] as datepart(month,uploaddate) persisted

) ON [partfunctionScheme]([Month])

 

GO

 

insert Rangepart

 

select '111','John','Sales','01/01/2011'

union

select '112','Marjori','Legal','02/01/2011'

union

select '113','Jack','Operations','03/01/2011'

union

select '114','Pravin','Sales','04/01/2011'

union

select '115','Lucas','Legal','05/01/2011'

union

select '116','Dave','Sales','06/01/2011'

union

select '117','Nehaa','IT','07/01/2011'

union

select '118','Rick','IT','08/01/2011'

union

select '119','Madahvi','Legal','09/01/2011'

union

select '120','Roselin','Marketing','10/01/2011'

union

select '121','Vikas','IT','11/01/2011'

union

select '122','Joanna','Marketing','12/01/2011'

 

Then we can see if our data went in correct partition by executing below query

 

select $partition.partfunction([Month]) partition, *

FROM [Rangepart]

Note that above e.g. is just for demonstration in practical use partitioning is done when we have huge volume of data.

Tags: , ,

SQL Server