Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

SQL Server 2008 requires unique non-clustered indexes created on a partition to include the partition field in the index definition.

I'm wondering why.

share|improve this question

Short answer:

If the clustering key would not be part of the key set then the index uniqueness across all partitions could not be guaranteed.

The long story:

The only way to guarantee uniqueness is by enforcing uniqueness via a B-Tree structure. With a B-Tree is really easy to enforce uniqueness, since any key has a deterministic logical position in the B-Tree (between its left neighbor, that is smaller key, and the right neighbor, which is a larger key). Since each key value can be inserted in a single position in the tree then uniqueness is easy to guarantee: simply check during insert if the position is already occupied by an existing row.

With partitioning uniqueness across all partitions is trickier, since there are many B-Tree structures involved (one for each partition). Each can guarantee uniqueness, within a partition, but together they cannot guarantee global uniqueness across all partitions: a key in partition X may also appear in partition Y. Therefore, in order to preserve the semantics of 'unique index', the clustering column must be part of the key. This way each key value deterministically bound to a specific partition: a key cannot appear in partition X and Y because the partitioning column is part of the key, therefore the key can belong to exactly partition X or Y.

@MarkStoreySmith asked: So why doesn't SQL Server check for uniqueness by navigating each of the B-tree structures, acquiring range locks where the value being tested is not present, and holding until each B-tree has been traversed? Seeking each B-Tree would be a performance hog (think 15,000 partitions to be checked at each insert). And also the locking complications (hopping from one B-Tree to the next) are significant, with huge potential for deadlocks.

share|improve this answer
Thus, "since there are many B-Tree structures involved..." is the real culprit in my opinion. If I want a unique index without the partitioned field then I shouldn't be forced to include the partitioned field. – bmk Oct 12 '11 at 20:21
@bmk: then create a non-partitioned unique index :) I understand that there is no perfect solution (good-bye fast partition switch!), but this is (one of) the price you pay when you choose partitioning. Is not a free lunch, and the 'price' is actually significantly larger than most realize. And, as you see, the price may actually percolate all the way to the data model (primary key must include partitioning column), which has huge implications on the ORM layer etc etc etc. I always recommend careful consideration when considering partitioning. – Remus Rusanu Oct 13 '11 at 0:14

you can create the non-clustered index to align the partition or not.

if you create the align partition index, in SQL Server 2005, the partition filed is actual the last filed in the index leaf page, although not in the create statements. I verified this by running 'dbcc page'.

share|improve this answer
Sorry, not sure I understand what you're explaining. – bmk Sep 19 '11 at 19:17

That could be simply because the index needs to be aligned with the partitions, as the index itself needs to be partitioned (i.e stored in separate locations on the drive). If the index is not aligned there will be cross partition pointers.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.