Step by step Table Partitioning in SQL Server Standard Edition

This blog provides a basic setup to perform table partitioning to a new table in a different server, I have not shown the file group creation and definition as they are understood by the name.

We can perform table starting from SQL Server 2016 SP1 of the standard edition it was only available in the Enterprise editions earlier.

First, we need to define a partition function with specific ranges.

RANGE RIGHT (i.e >=Jan 01 2017)
RANGE LEFT (i.e <=Dec 31 2016)

Hope you understood the range right and left. The function should be defined on the Date parameter.

-- Create the Partition Function
CREATE PARTITION FUNCTION MAF (datetime)
AS RANGE RIGHT FOR VALUES
('2017-05-01','2017-06-01','2016-07-01', '2017-08-01', '2017-09-01','2017-10-01');

Now, Let's create a scheme which helps us map the filegroups to segregate the data.
The Primary filegroup here will contain all the data prior to 2017-05-01 and all the data after 2017-10-01 is stored in FG102017.

-- Create the Partition Scheme
CREATE PARTITION SCHEME MAS
AS PARTITION MAF TO ([PRIMARY],[FG052017],[FG062017],[FG072017],[FG082017],[FG092017],[FG102017]);


How let's create the table using the Scheme create on a DateTime column.

CREATE TABLE [dbo].[Log_Error](
[ErrorId] [uniqueidentifier] NOT NULL,
[Application] [nvarchar](60) NOT NULL,
[Host] [nvarchar](50) NOT NULL,
[Type] [nvarchar](100) NOT NULL,
[Source] [nvarchar](60) NOT NULL,
[Message] [nvarchar](500) NOT NULL,
[User] [nvarchar](50) NOT NULL,
[StatusCode] [int] NOT NULL,
[TimeUtc] [datetime] NOT NULL,
[Sequence] [int] IDENTITY(1,1) NOT NULL,
[AllXml] [ntext] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Sequence] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON MAS(TimeUtc);
GO

The Data to be pushed in the table is loaded from source server using a linked server WINSQL2016T2, The Indexes play a major roll in moving the data in multiple filegroups.

-- Insert test data
INSERT INTO [Log_Error]
SELECT ErrorId,Application,Host,Type,Source,Message,User,StatusCode,TimeUtc,AllXml FROM [WINSQL2016T2].Market.dbo.[Log_Error]
select top 1 timeutc from [WINSQL2016T2].Market.dbo.[Log_Error] order by timeutc desc where TimeUtc between '2017-01-01' and '2018-01-01'

There are two types of index partitions

• Aligned Indexes: With me creating the indexes on the Partition Scheme.
• NON-Aligned indexes: Here the Index will be created explicitly on the primary filegroup.

Depending on below points the index should be created.

• Partitioned indexes perform better when you are aggregating data or scanning partitions.

• If you need to locate a single, specific record, nothing performs better than a non-partitioned index on that column.

Below query can we used to verify the table partitions.
Just replace the table name.

***********************************************************
SELECT  OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName  ,OBJECT_NAME(pstats.object_id) AS TableName  ,ps.name AS PartitionSchemeName  ,ds.name AS PartitionFilegroupName  ,pf.name AS PartitionFunctionName  ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange  ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary  ,prv.value AS PartitionBoundaryValue  ,c.name AS PartitionKey  ,CASE     WHEN pf.boundary_value_on_right = 0     THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))     ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))  + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))  END AS PartitionRange  ,pstats.partition_number AS PartitionNumber  ,pstats.row_count AS PartitionRowCount  ,p.data_compression_desc AS DataCompressionFROM sys.dm_db_partition_stats AS pstatsINNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_idINNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_idINNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_idINNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_idINNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_idINNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_idLEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)WHERE pstats.object_id = OBJECT_ID('--TableName')ORDER BY TableName, PartitionNumber;**********************************************************************

Hope this is informative!!!

                 

By Mohammed Adil