partitioning with unique non-clustered index.

Question

why would there be a requirement that a unique non-clustered index on a partition include the partitioning key in the index definition?

In other words, you can't create a unique non-clustered index on a partition scheme if the partitioned field isn't included in the definition of the non-clustered index.

Answer 1

Hi,

 

From Technet:

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.

The key part there is to make sure that the index is aligned with the base table. If it doesn't contain the partitioning column it can't line the index up with the partition scheme the table is built on.

Thanks,

Stephen

---

Please click "Mark as Answer" if you found my post helpful. Thanks, Stephen.

Answer 2

If the partitioning column did not belong to index keys, the server would have to look at all partitions (i.e. traverse multiple rowsets) during insert, to determine if there are no duplicate keys. This would be very inefficient. The restriction guarantees that the only partition that might contain a row with a duplicate key is the one where the data is actually inserted.

And unlike with non-unique indexes, the server cannot just add the partitioning column to the key by itself, because that would relax the uniqueness constraint.
For example: a table has two columns (A int, B int). We want a unique index on A. If we partition the index on B and the server adds B to the keys, you could insert values (1, 1) and (1, 2), making A no longer unique.

For these two reasons, the server will return an error if you try to partition a unique index on a non-key column.

 

Answer 3

Hi All

this is an interesting point.

Logically, you should add the partition column just to a not-unique index, as when you do slinding windows trough alignment you could not remove an entry that points to another partition as well. On the opposite an entry of an unique index should point just to that partition you are removing, so enabling you to do that without breaking index consistency.

The fact is that on a not unique cluster index the system add the partition column itself, so to have everything matching; on a unique wherease it demands the column to be explicitely specified. This happens when you create an out table too (for swith out).

Also adding the column is useful to locate the entry on the right partition schema group, though this could be spot somehow based on the data that you are indexing, which must belog to a precise partition, but perahps this add an overhead on calculation, and that is why the column is asked even when not logically needed.

Regards,

             Marco

Answer 4

If your requirement is to create a unique index that doesn't include the partitioning key, you can create a non-partitioned index even of the table and other indexes are partitioned.  The downside is this method will forego some of the manageability benefits of partitioning, such as sliding window maintenance or maintaining individual partitions of this index.

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/