Partitioned Tables and Indexes

You can create a partitioned table or index in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL. The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable.

On data tables with a large number of records, such as those found in the data warehouse application consider partitioning the indexes across multiple file groups. Partition indexes divide the number of records that they organize based off of an attribute in the data. Date range is a very common attribute used in partitioning, so all the historic records from 2016 will be indexed together, and records from 2017 will get picked up by a separate index. This way, as updates and additions to the records occur in 2017, a much smaller index will need to be updated. And the 2016 index will likely never need to be updated again. 

Creating a partitioned table or index typically happens in four parts:

1. Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
2. Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column.
3. Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
4. Create or modify a table or index and specify the partition scheme as the storage location.

Aligned index

An index that is built on the same partition scheme as its corresponding table. When a table and its indexes are in alignment, SQL Server can switch partitions quickly and efficiently while maintaining the partition structure of both the table and its indexes. An index does not have to participate in the same named partition function to be aligned with its base table. However, the partition function of the index and the base table must be essentially the same, in that:

1. The arguments of the partition functions have the same data type.
2. They define the same number of partitions.
3. They define the same boundary values for partitions.

Partitioning Clustered Indexes

When partitioning a clustered index, the clustering key must contain the partitioning column. When partitioning a nonunique clustered index, and the partitioning column is not explicitly specified in the clustering key, SQL Server adds the partitioning column by default to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column.

Partitioning NonClustered Indexes

When partitioning a unique nonclustered index, the index key must contain the partitioning column. When partitioning a nonunique, nonclustered index, SQL Server adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table. SQL Server does not add the partitioning column to the index if it is already present in the index.

Good Practice

• The Clustered Index must contains the partition column.
• Original PK is non-clustered.

CREATE TABLE [dbo].[tbTable]
(
	CONSTRAINT XPK_tbTable PRIMARY KEY NONCLUSTERED 
	(
		TableId ASC
	)
)
ON pstbTable(dYYYYMM);


CREATE CLUSTERED INDEX IX_tbTable_dYYYYMM ON dbo.tbTable (dYYYYMM)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON pstbTable(dYYYYMM);

CREATE TABLE [dbo].[tbTable]
(
	CONSTRAINT [PK_tbTable] PRIMARY KEY CLUSTERED 
	(
		TableId ASC, dYYYYMM ASC
	)
	WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
	ON [pstbTable](dYYYYMM)
)
ON pstbTable(dYYYYMM);

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-2017