Partitioned tables and indexes in SQL Server(ANO25)

#Partitionedtables, #indexes, #SQLServer?

Partitioning tables and indexes in SQL Server is a way to organize table or index data into smaller units based on the values of certain columns.

We can use SQL Server Management Studio or Transact-SQL to partition tables and indexes in SQL Server. This means that the data in tables and indexes is divided into smaller pieces that can be stored in different filegroups within a database, or in a single filegroup. Partitioning can help you manage and scale large tables and indexes more easily.

Creating a partitioned table or index usually involves three or four steps:

1. Optionally?create a filegroup?or filegroups and corresponding data files that will hold the partitions specified by the partition scheme. The main reason to place partitions on multiple filegroups is to ensure you can independently perform backup and restore operations on filegroups. If this is not required, you may choose to assign all partitions to a single filegroup, using either an existing filegroup, such as?PRIMARY, or a new filegroup with related data file(s). In nearly all scenarios, you will achieve all?benefits of partitioning?whether or not you use multiple filegroups.
2. Create a?partition function?that maps the rows of a table or index into partitions based on the values of a specified column. You can use a single partition function to partition multiple objects.
3. Create a?partition scheme?that maps the partitions of a partitioned table or index to one filegroup or to multiple filegroups. You can use a single partition scheme to partition multiple objects.
4. Create or alter a table or index and specify the partition scheme as the storage location, along with the column that will serve as the partitioning column.

Now let's have demo:

In this demo, I want to create a table ("FactInternetSales2") and partition it according to its "OrderDate".

Create a database and create a table by coping FactInternetSales from AdventureWorksDW2019

USE MASTER
ALTER DATABASE [DB1]
SET SINGLE_USER?
WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE IF EXISTS DB1
-----------------------------------
CREATE DATABASE [DB1]
GO
----------------------------------
USE DB1

SELECT * INTO FactInternetSales2?
FROM AdventureWorksDW2019.DBO.FactInternetSales
--Add a column to create Primary key
Alter Table FactInternetSales2
add? ID int identity
GO
ALTER TABLE FactInternetSales2 ADD CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED?
(	
	ID
)
GO        

As we want to create a partition based on OrderDate, it's necessary to find out the number of years we have on our table and create Filegroups according to them.

SELECT
	DATEPART(YEAR,OrderDate) AS [Year]??
FROM FactInternetSales2 GROUP BY? DATEPART(YEAR,OrderDate)
ORDER BY DATEPART(YEAR,OrderDate)?        

Create filegroups:

According to the image above, we need 5 file groups for the existing data and I will add an extra file group for the year 2015.

ALTER DATABASE DB1 ADD FILEGROUP FG2010
ALTER DATABASE DB1 ADD FILEGROUP FG2011
ALTER DATABASE DB1 ADD FILEGROUP FG2012
ALTER DATABASE DB1 ADD FILEGROUP FG2013
ALTER DATABASE DB1 ADD FILEGROUP FG2014
ALTER DATABASE DB1 ADD FILEGROUP FG2015
GO
--Add DataFiles:
GO
ALTER DATABASE DB1
	ADD FILE (NAME=N'Data2010',FILENAME=N'E:\DUMP\Data2010.ndf') TO FILEGROUP FG2010
GO
ALTER DATABASE DB1
	ADD FILE (NAME=N'Data2011',FILENAME=N'E:\DUMP\Data2011.ndf') TO FILEGROUP FG2011
GO
ALTER DATABASE DB1
	ADD FILE (NAME=N'Data2012',FILENAME=N'E:\DUMP\Data2012.ndf') TO FILEGROUP FG2012
GO
ALTER DATABASE DB1
	ADD FILE (NAME=N'Data2013',FILENAME=N'E:\DUMP\Data2013.ndf') TO FILEGROUP FG2013
GO
ALTER DATABASE DB1
	ADD FILE (NAME=N'Data2014',FILENAME=N'E:\DUMP\Data2014.ndf') TO FILEGROUP FG2014
GO
ALTER DATABASE DB1
	ADD FILE (NAME=N'Data2015',FILENAME=N'E:\DUMP\Data2015.ndf') TO FILEGROUP FG2015
GO        

CREATE PARTITION FUNCTION:

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )??

AS RANGE [ LEFT | RIGHT ]??

FOR VALUES ( [ boundary_value [ ,...n ] ] )??

Arguments

partition_function_name

Is the name of the partition function. Partition function names must be unique within the database and comply with the rules for?identifiers.

input_parameter_type

Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except?text,?ntext,?image,?xml,?timestamp,?varchar(max),?nvarchar(max),?varbinary(max), alias data types, or CLR user-defined data types.

The actual column, known as a partitioning column, is specified in the CREATE TABLE or CREATE INDEX statement.

boundary_value

Specifies the boundary values for each partition of a partitioned table or index that uses?partition_function_name. If?boundary_value?is empty, the partition function maps the whole table or index using?partition_function_name?into a single partition. Only one partitioning column, specified in a CREATE TABLE or CREATE INDEX statement, can be used.

LEFT?| RIGHT

Specifies to which side of each boundary value interval, left or right, the?boundary_value?[?,...n?] belongs, when interval values are sorted by the Database Engine in ascending order from left to right. If not specified, LEFT is the default.

I want to create a range left function with DateTime datatype

CREATE PARTITION FUNCTION PF1 (DATETIME)
AS RANGE LEFT
FOR VALUES('2010-12-31 23:59:59.997','2011-12-31 23:59:59.997',
		? ?'2012-12-31 23:59:59.997','2013-12-31 23:59:59.997','2014-12-31 23:59:59.997')
GO?        

CREATE PARTITION SCHEME?

Syntax

CREATE PARTITION SCHEME partition_scheme_name??

AS PARTITION partition_function_name??

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )??

Arguments

partition_scheme_name

Is the name of the partition scheme. Partition scheme names must be unique within the database and comply with the rules for?identifiers.

partition_function_name

Is the name of the partition function using the partition scheme. Partitions created by the partition function are mapped to the filegroups specified in the partition scheme.?partition_function_name?must already exist in the database. A single partition cannot contain both FILESTREAM and non-FILESTREAM filegroups.

ALL

Specifies that all partitions map to the filegroup provided in?file_group_name, or to the primary filegroup if?[PRIMARY]?is specified. If ALL is specified, only one?file_group_name?can be specified.

file_group_name?|?[?PRIMARY?]?[?,...n]

Specifies the names of the filegroups to hold the partitions specified by?partition_function_name.?file_group_name?must already exist in the database.

If?[PRIMARY]?is specified, the partition is stored on the primary filegroup. If ALL is specified, only one?file_group_name?can be specified. Partitions are assigned to filegroups,starting with partition 1, in the order in which the filegroups are listed in [?,...n]. The same?file_group_name?can be specified more than one time in [?,...n]. If?n?is not sufficient to hold the number of partitions specified in?partition_function_name, CREATEPARTITION SCHEME fails with an error.

If?partition_function_name?generates fewer partitions than filegroups, the first unassigned filegroup is marked NEXT USED, and an information message displays naming the NEXT USED filegroup. If ALL is specified, the sole?file_group_name?maintains its NEXT USED property for thispartition_function_name. The NEXT USED filegroup will receive an additional partition if one is created in an ALTER PARTITION FUNCTION statement. To create additional unassigned filegroups to hold new partitions, use ALTER PARTITION SCHEME.

When you specify the primary filegroup in?file_group_name?[?,...n], PRIMARY must be delimited, as in?[PRIMARY], because it is a keyword.

In this part, I'm going to create a partition scheme that maps each partition to a different filegroup.

CREATE PARTITION SCHEME PS1 AS PARTITION PF1
	TO (FG2010,FG2011,FG2012,FG2013,FG2014,FG2015)
GO	        

Till now we prepared the partition Function and Scheme, and now we can assign our table to this scheme. To do this I will create a clustered index.

REATE UNIQUE CLUSTERED INDEX IX_Cluster ON
	 FactInternetSales2(OrderDate,ID)ON PS1(OrderDate)
GO        

Use this script to see the result:

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName,
? ? p.partition_number AS PartitionNumber, f.name AS PartitionFunctionName, p.rows AS Rows, rv.value AS BoundaryValue,?
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'?
ELSE
? ? CASE WHEN f.boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>='?
? ? ? ? WHEN f.boundary_value_on_right = 0 THEN '>'?
? ? ? ? ELSE '>='?
? ? END + ' ' + ISNULL(CONVERT(varchar(64), rv2.value), 'Min Value') + ' ' +?
? ? ? ? CASE f.boundary_value_on_right WHEN 1 THEN 'and <'?
? ? ? ? ? ? ? ? ELSE 'and <=' END?
? ? ? ? + ' ' + ISNULL(CONVERT(varchar(64), rv.value), 'Max Value')?
END AS TextComparison
FROM sys.tables AS t??
JOIN sys.indexes AS i??
? ? ON t.object_id = i.object_id??
JOIN sys.partitions AS p??
? ? ON i.object_id = p.object_id AND i.index_id = p.index_id? ?
JOIN? sys.partition_schemes AS s? ?
? ? ON i.data_space_id = s.data_space_id??
JOIN sys.partition_functions AS f? ?
? ? ON s.function_id = f.function_id??
LEFT JOIN sys.partition_range_values AS r? ?
? ? ON f.function_id = r.function_id and r.boundary_id = p.partition_number??
LEFT JOIN sys.partition_range_values AS rv
? ? ON f.function_id = rv.function_id
? ? AND p.partition_number = rv.boundary_id? ? ?
LEFT JOIN sys.partition_range_values AS rv2
? ? ON f.function_id = rv2.function_id
? ? AND p.partition_number - 1= rv2.boundary_id
WHERE?
? ? t.name = 'FactInternetSales2'
? ? AND i.type <= 1?
ORDER BY t.name, p.partition_number;

?        

Now let's compare the ordinary table and partitioned table:

--Query1
USE DB1
SELECT
*
FROM FactInternetSales2 --This table is a Partioned table
WHERE OrderDate BETWEEN '2013-06-01' AND '2013-06-30'
GO
--Query2?
SELECT
*
FROM AdventureWorksDW2019.dbo.FactInternetSales --This table is an ordinary table without any PARTITIONED Index
WHERE OrderDate BETWEEN '2013-06-01' AND '2013-06-30'
GO?        

As you can see while the cost of the partitioned table is 8%, this figure for non partitioned table is 92%.