2

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?

Improve this question
6
  • 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. Commented 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. Commented 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. Commented 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. Commented 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 :-) Commented Feb 15, 2016 at 18:13

1 Answer 1

Reset to default
5

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.

Improve this answer
5
  • 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? Commented 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. Commented 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. Commented Feb 15, 2016 at 17:56
  • Locking... that was the bit I oversaw. Commented 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. Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.