The primary goal of partitioning is to manage large tables more efficiently.
However, the partitioned objects can also improve performance by reducing the overall response time of DML and DDL queries.
The large table, in one way, is divided into multiple tables based on a defined criteria differing in range of rows.
Prior to SQL Server 2016 SP1, partitioned tables and indexes were not available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and Supported Features for SQL Server 2016.


Benefits of Partitioning


Partitioning can provide tremendous benefit to a wide variety of applications by improving manageability, availability, and performance. It is not unusual for partitioning to greatly improve the performance of certain queries or maintenance operations. Moreover, partitioning can greatly simplify common administration tasks.

Partitioning can enhance query performance, but there is no guarantee, The reason for this is you can only partition on a single column (the partitioning key) and this is what will be used for partition elimination." And, "In summary, partitioning is mostly for improved maintenance, fast loads, fast deletes and the ability to spread a table across multiple filegroups; it is not primarily for query performance.

Creating a Partitioned Table


To create a partitioned table there are a few steps that need to be done:

1. Create additional filegroups if you want to spread the partition over multiple filegroups.
2. Create a Partition Function
3. Create a Partition Scheme
4. Create the table using the Partition Scheme


Step 1

To start with, let's create a sample database which contains several filegroups for our demo purposes.

--Create the Database
CREATE DATABASE SE_DEMO;
--Now Create Five Filegroups
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange01;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange02;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange03;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange04;
ALTER DATABASE SE_DEMO ADD filegroup FGDayRange05;

Step 2

Now let's create the files for each filegroup.

--Now Add One File to Each Filegroup
ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_01, FILENAME = 'C:\FGDayRange_01.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange01;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_02, FILENAME = 'C:\FGDayRange_02.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange02;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_03, FILENAME = 'C:\FGDayRange_03.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange03;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_04, FILENAME = 'C:\FGDayRange_04.NDF', SIZE = 5MB, FILEGROWTH = 1MB
)
TO FILEGROUP FGDayRange04;

ALTER DATABASE SE_DEMO
ADD FILE
(
NAME = FGDayRange_05, FILENAME = 'C:\FGDayRange_05.NDF', SIZE = 5MB, FILEGROWTH = 1MB )
TO FILEGROUP FGDayRange05;

USE SE_DEMO
GO

Step 3 - Create Partition Function

This creates a range of values for the partition. This will create six partitions:

--Now Create a Partition Function
CREATE Partition FUNCTION pfFiveDayRange (Datetime)
AS RANGE RIGHT FOR VALUES
('2017-10-01', '2017-10-02', '2017-10-03', '2017-10-04', '2017-10-05')
GO

Step 4 - Create partition scheme

Partition scheme determines where each of the partitions will reside.

--Now create a Partition Scheme to Hold Five Filegroups
--These Five Filegroups will hold the data for the five dates defined in Partition Function.
CREATE PARTITION SCHEME psFiveDayRange
AS PARTITION pfFiveDayRange
TO ([PRIMARY], [FGDayRange01], [FGDayRange02], [FGDayRange03], [FGDayRange04],
[FGDayRange05]);

Step 5 - Create a new partitioned table

This creates the table using the above created partition scheme psFiveDayRange that was created in step 4. The column businessDate is used to determine what data gets placed in which partition/filegroup.

--Now create a Partitioned Table and clustered index on partition key.
CREATE TABLE SE_DEMO_Partitions
(ID INT IDENTITY,
businessDate DATETIME NOT NULL)
ON psFiveDayRange (businessDate);
GO
CREATE CLUSTERED INDEX IDXC_businessDate ON SE_DEMO_Partitions(businessDate)

Step 6

Adding Data to Partitioned Table

After the table has been setup as a partitioned table, when you enter data into the table SQL Server will handle the placement of the data into the correct partition automatically for you.

So, based on the above setup if we run the below commands the data will be placed in the appropriate partition as shown below.

SET NOCOUNT ON
--Load 50 records with each date
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-01')
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-02')
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-03')
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-04')
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-05')
GO 50
VALUES ('2017-10-02')
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-03')
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-04')
GO 50
INSERT INTO SE_DEMO_Partitions (businessDate)
VALUES ('2017-10-05')
GO 50

To determine what exists in each partition you can run the following command:

-- By Now you have 250 records (50 Each Date) sitting on each Filegroup.
-- Let's check this out.
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName
,OBJECT_NAME(i.object_id) AS ObjectName
,ISNULL(QUOTENAME(i.name),'Heap') AS IndexName
,p.partition_number AS PartitionNumber
,fg.name AS Filegroup_Name
,rows AS 'Rows'
,au.total_pages AS 'TotalDataPages'
,CASE boundary_value_on_right
WHEN 1 THEN 'less than'
ELSE 'less than or equal to'
END AS 'Comparison'
,value AS 'ComparisonValue'
,p.data_compression_desc AS 'DataCompression'
,p.partition_id
,ps.name AS PartitionScheme
,f.name AS PartitionFunction
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id
JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM sys.allocation_units
GROUP BY container_id) AS au ON au.container_id = p.partition_id
JOIN sys.tables t ON p.object_id = t.object_id
WHERE OBJECT_NAME(i.object_id) ='SE_DEMO_Partitions'
ORDER BY ObjectName,i.name,p.partition_number;
GO

Output of above query will be something a below:

In addition to determining the number of rows that are in each of the partitions we can also see how fragmented each of these partitions are. By using the DMV sys.dm_db_index_physical_stats we can get this information.

SELECT object_id, partition_number,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID
(N'dbo.SE_DEMO_Partitions'),
NULL , NULL, NULL);

Output of above query Something like below:


Based on the above result from sys.dm_db_index_physical_stats, you can rebuild an index for a partition. Here is an example of the code that could be used to rebuild index IDXC_businessDate only on partition #5.

ALTER INDEX IDXC_businessDate
ON dbo.SE_DEMO_Partitions
REBUILD Partition = 5;
GO

As you can see this is a great enhancement to SQL Server.

I hope this gives you an idea of how helpful data partitioning can be to move sets of data and maintain large datasets. Although this is very simple on the outside there are several things that need to think about before implementing a data partitioning scheme.