According to https://stackoverflow.com/questions/4332982/do-clustered-indexes-have-to-be-unique

The answer is yes. In the sense that SQL Server will make it unique by appending 4-bytes to key. This makes sense given that clustered index is used for data addressing purposes (Correct?).

According to my research, it seems that SQL Server requires non-clustered indexes to be unique as well. And when non-unique column is used, SQL Server appends a 4-byte "uniquefier" value to make it unique. Is that correct?

Why does SQL Server require non-clustered index to use unique keys?

  • 1
    So it can locate the row in the index. Just because a non-clustered index might not have its own unique key, and in fact two rows may be indistinguishable for you, SQL Server still needs to be able to tell two rows apart. Feb 15, 2016 at 17:38
  • I understand the "locator" problem. But representation could be as simple as: (non-unique-value) -> [locator1, locator2, locator3] rather than: [(non-unique-value+4bytes, locator1), (non-unique-value+4bytes, locator2), (non-unique-value+4bytes, locator3)]. That's the bit I don't get.
    – Gatis
    Feb 15, 2016 at 17:53
  • About nonclustered: on leaf level clustered index key is stored (with 4-bytes appendix if applied). Thus if you make clustered index non-unique, you must account that physical key will be 4 bytes bigger and every non-clustered index will require additional space for it too.
    – Ivan Starostin
    Feb 15, 2016 at 17:53
  • I guess you could consider designing your own system with simpler representation. In the meantime, this is how SQL Server works. Feb 15, 2016 at 18:04
  • 1
    As Aaron said, this is how SQL Server works. Other DBMSes might do it differently, e.g. a Non-Unique Secondary Index in the Teradata DBMS stores the data the way you wanted: index value -> array of ROWIDs for this value :-)
    – dnoeth
    Feb 15, 2016 at 18:13

1 Answer 1


Probably it simplifies the engine a lot because you can now address individual rows.

Imagine, you are indexing on a IsMale bit column and there are 1 million "male" values. Now you want to address exactly one of those rows to delete it. The index has 1 million rows that just say true and nothing more. I'm sure it's possible but uniqueness makes the design much more elegant.

Locking requires index rows to be uniquely identifiable.

  • IMHO, it makes implementation horribly inefficient (I am sure that's NOT the case though). Intuitively, I'd expect IsMale bit to be represented twice (True, False) and each value pointing to a list of pointers to on the CLUSTERED INDEX. Why that's not the case?
    – Gatis
    Feb 15, 2016 at 17:47
  • This is not how b-trees work in SQL Server. An index (b-tree) is just a copy of the base table with a subset of columns and sorted differently. It's a "sorted list". This is a rather nice and general model because all indexes are the same. SQL Server does not distinguish (in a a relevant way) between CI and NCI on a physical level.
    – usr
    Feb 15, 2016 at 17:54
  • What you propose is theoretically possible. It would be a form of data compression built into the b-tree. The space savings depend on how big index prefixes typically are.
    – usr
    Feb 15, 2016 at 17:56
  • Locking... that was the bit I oversaw.
    – Gatis
    Feb 15, 2016 at 18:02
  • Imagine your scenario of a "list of pointers pointing to the clustered index". If I did ANY sort of physical address change, I'd have to update those pointers or they'd be invalid. This means even if there wasn't a data change to the CI the NCIs would all have to be updated. That's horribly inefficient and the basis of how heaps work. In fact, heaps have forwarding/forwarded records to cut down on exactly that issue. Feb 16, 2016 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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