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. – Aaron Bertrand Feb 15 '16 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 '16 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 '16 at 17:53
  • I guess you could consider designing your own system with simpler representation. In the meantime, this is how SQL Server works. – Aaron Bertrand Feb 15 '16 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 '16 at 18:13

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 '16 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 '16 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 '16 at 17:56
  • Locking... that was the bit I oversaw. – Gatis Feb 15 '16 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. – Sean Gallardy - Retired User Feb 16 '16 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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