SQL Server Partition Table index

Indexes are very effective for improving query performance, so it is generally important to consider indexing a partitioned table, indexing a partitioned table and indexing the normal table all the time, but its behavior differs from the normal index.

By default, the indexes created in the partitioned table use the same partition schema and partitioning columns as the partitioned table, so that the indexes are aligned in the table. Aligning a table to its index makes administration easier, especially for sliding window scenarios. To initiate partition switching, all indexes on the table must be aligned.

Although partitioned indexes can be implemented separately from the base table of a partitioned index, it is common practice to design a partitioned table and then create an index for that table. When you do this, SQL Server automatically partitions the index using the same partitioning scheme and partitioning column as the table. Therefore, the partitioning method of the index is essentially the same as the partitioning of the table. This causes the index to be "aligned" with the table. If you specify a different partitioning scheme or a separate filegroup to store the index when you create it, SQL Server does not align the index with the table .

 

partitioning a clustered indexWhen you partition a clustered index, the clustered key must contain a partitioning column.

When partitioning a non-unique clustered index, if the partitioning column is not explicitly specified in the clustered key, SQL Server adds the partitioning column to the clustered index key list by default. If the clustered index is unique, you must explicitly specify that the clustered index key contains the partitioning column.

Partitioning a nonclustered index

When partitioning a unique nonclustered index, the index key must contain the partitioning column.

when partitioning a non-unique nonclustered index, by default SQL Server adds the partitioning column as a non-key (inclusive) column of the index to ensure that the index is aligned with the base table. If the partitioning column already exists in the index, SQL Server will not add the partitioning column to the index

SQL Server Partition Table index