• Grant Fritchey - Tuesday, May 16, 2017 5:57 AM

    Bill Talada - Tuesday, May 16, 2017 5:42 AM

    Personally, I would never say all clustered indexes are unique.  It is a philosophical issue but I believe Grant's statement will mislead all beginners.  Technically everything in the universe is unique.  Even non-clustered indexes represent every row in a table and therefore are unique.  Sorry Grant, no offense, but sometimes we'll disagree.

    No worries. People honestly disagree all the time.

    I'll still push back a little on this one. Clustered indexes are special (one might even say unique) in that they define the storage of the data, not simply a set of keys. And yeah, technically nonclustered indexes are unique at the leaf level, but only by virtue of the fact that they have the unique key values of the clustered index stored with them (or the RID for heaps). Before writing any of this, I checked Kalen Delaney's book to validate it, and she emphasizes the unique nature of clustered indexes as well (yeah, arguing from authority sucks, but I know I screw up a lot, so I validate where I can).

    You do make a valid point though.

    In Microsoft SQL Server 2012 Internals, you'll find the argument Grant's referring to on page 316.  Either make it unique or SQL Server will do it for you.  Personally, I think it's better to make it unique yourself and not have SQL consume extra bytes on the row for you.