Table Partitions

Introduced in SQL Server 2005, before that partitioned views are available. A table partition means “divide a large table physically into one or more file groups in the same database, but logically shown as a single table”.

Advantages: 
You can Insert or access the data from a partitioned table quickly and efficiently.
E.g. if you trigger a query, it will perform the actions on respective partitions instead of doing on whole table.

You can perform maintenance operations on one or more partitions more quickly.
E.g. you can perform backup operations on individual file groups or compress the data in one or more partitions.

The query performance will improve.
E.g. when a complex query triggered on a table, instead of involving entire table involves only respective partitions. ..etc

Implementing partition mechanism:
Three components will be involved in a table partition.
1. Partition function
2. Partition scheme
3. Partitioning column

1. Partition Function:
It defines how the rows of a table are divided into multiple file groups based on the values in partitioning column. Here the rows Range will be either LEFT or RIGHT. For example, your table has 3 years of data (1981, 1982, and 1983) and we are dividing the table in to 3 partitions based on the Partitioning column values (HireDate in Employee table). Now your requirement is Partition1 contains the data before 1982, Partition2 contains only 1982, Partition3 contains 1983 and above.

LEFT RANGE: Count the Partitioning column values from LEFT side.

RIGHT RANGE: Count the Partitioning column values from RIGHT side.

2. Partition scheme:

It will group all the file groups and maps Partition Function to all file groups.

3. Partitioning column:

It is a column in the table and it will divide the rows to partitions based on some criteria. For example, Employee table have a column named as “HireDate”, it will divides the rows to partitions based on years. Means Partition1 contains only Employee who joined in 1981, Partition2 contains 1982 and so on.

Example:

 I have created a database Test_DB with 3 data files on 3 file groups. And each file group on different drives

PRIMARY on D drive,

FileGroup2 on E drive,

FileGroup3 on F drive.

CREATE DATABASE [Test_DB]

ON  PRIMARY                  --Default fileGroup ‘Primary’ on D drive

          ( NAME = N'Test_DB',

          FILENAME = N'D:\Radha\Test_DB.mdf' ,

          SIZE = 3072KB ,FILEGROWTH = 1024KB ),

         

 FILEGROUP [FileGroup2]      --Second fileGroup ‘FileGroup2’ on E drive

          (NAME = N'Test_DB1',

          FILENAME = N'E:\Radha\Test_DB1.ndf' ,

          SIZE = 3072KB ,FILEGROWTH = 1024KB ),

         

 FILEGROUP [FileGroup3]           --Third fileGroup ‘FileGroup3’ on F drive

          ( NAME = N'Test_DB2',

          FILENAME = N'F:\Radha\Test_DB2.ndf' ,

          SIZE = 3072KB ,FILEGROWTH = 1024KB )

         

 LOG ON ( NAME = N'Test_DB_log', --Log file on T drive

          FILENAME = N'T:\Radha\Test_DB_log.ldf' ,

          SIZE = 1024KB , FILEGROWTH = 10%)

GO

 I have verified files and file groups information by the below script,

SELECT FG.name as [FileGroupName],DF.name as [DBfileName],DF.physical_name as [DBfilePath]

FROM sys.filegroups FG inner join sys.database_files DF

on FG.data_space_id = DF.data_space_id

WHERE FG.type = 'FG' or DF.type_desc = 'ROWS'

GO

Step1: Create a Partition Function with RIGHT RANGE. So that, the function can divide the rows to respective partitions for each complete year,

Use [Test_DB]

GO

CREATE PARTITION FUNCTION HDFunction (datetime)

AS RANGE RIGHT FOR VALUES ( '1982-01-01', '1983-01-01')

GO

Step2: Create a partition Scheme, it will group all file groups and map the HDFunction to all file groups,

Use [Test_DB]

GO

CREATE PARTITION SCHEME Test_PartitionScheme

AS PARTITION HDFunction

TO ([PRIMARY], [FileGroup2], [FileGroup2])

GO

Step3: Create a partitioned Table, here am going to partition Employee table to 3 files groups on Test_DB as,

Use [Test_DB]

GO

CREATE TABLE [dbo].[Employee]

 (EMPNO INT PRIMARY KEY,

 ENAME VARCHAR(20),

 JOB VARCHAR(20),

 MGR INT,

 HIREDATE DATETIME,

 SAL MONEY,

 COMM MONEY,

 DEPTNO int);

 GO

ON Test_PartitionScheme (HIREDATE);

GO

Step4: Insert values to Employee table with different HIREDATES as,

INSERT INTO [dbo].[Employee] VALUES

 (7369, 'SMITH', 'CLERK', 7902, '17-DEC-1980', 800, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-1981', 1600, 300, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-1981', 1250, 500, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7566, 'JONES', 'MANAGER', 7839, '2-APR-1981', 2975, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7698, 'BLAKE', 'MANAGER', 7839, '1-MAY-1981', 2850, NULL, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7782, 'CLARK', 'MANAGER', 7839, '9-JUN-1981', 2450, NULL, 10);

 INSERT INTO [dbo].[Employee] VALUES

 (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7839, 'KING', 'PRESIDENT', NULL, '17-NOV-1981', 5000, NULL, 10);

 INSERT INTO [dbo].[Employee] VALUES

 (7844, 'TURNER', 'SALESMAN', 7698, '8-SEP-1981', 1500, 0, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7900, 'JAMES', 'CLERK', 7698, '3-DEC-1981', 950, NULL, 30);

 INSERT INTO [dbo].[Employee] VALUES

 (7902, 'FORD', 'ANALYST', 7566, '3-DEC-1981', 3000, NULL, 20);

 INSERT INTO [dbo].[Employee] VALUES

 (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, NULL, 10);

Step5:  we can verify the rows in the different partitions as,

Use [Test_DB]

GO

select partition_id, index_id, partition_number, Rows

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Employee'

GO

For more info click on Partitions...