Partitioning tables and indexes

Today, I review the knowledge about partition table. Just note here to easily refer:

Both tables and indexes can be partitioned. More specifically, you can partition the following:

• A table without a clustered index (heap)
• A clustered index
• A unique index
• A nonclustered index

When partitioning a clustered index, the column that has been specified as the partitioning column must be included in the clustering key. If you are partitioning a clustered or nonclustered index that is not unique, the partitioning column is not required as part of the key. However, if you do not include it, SQL Server will add it to the index by default. With regard to unique indexes, clustered or nonclustered, you must include the partitioning column as part of the unique index key.

USE [AdventureWorks2012];
CREATE CLUSTERED INDEX CIX_PurchaseOrderHeader_OrderDate
ON dbo.PurchaseOrderHeader(OrderDate)
WITH(DROP_EXISTING = ON)
ON schPOOrderDate(OrderDate);

schPOOrderDate here is a partition scheme.