2

It is possible to define non-unique columns as clustered as well as non-clustered indexes. However, SQL Server adds a 4 byte integer to the indexed columns in case of a clustered index, if the column is not defined as unique. This is done to keep the "uniqueness" of the record internally even though two or more records may have the value for that column. Why isn't this integer necessary in case of a non-clustered index?

Ben
  • 51,770
  • 36
  • 127
  • 149
SexyBeast
  • 7,913
  • 28
  • 108
  • 196

2 Answers2

7

A non-clustered index already includes the clustered index column so it can reference the exact row that it correlates to. Hence with the uniquifier on the clustered index, the non-clustered index would also include the uniquifier.

A good explanation here: Understanding and Examining the Uniquifier in SQL Server

Kash
  • 8,799
  • 4
  • 29
  • 48
  • But the non-clustered index has no unique identifier even when there is no clustered index. – SexyBeast Sep 07 '12 at 17:45
  • 2
    @Cupidvogel - For an NCI on a heap the RID is included. That is unique. SQL Server ensures that NCI keys are always unique as well [as discussed here](http://sqlblog.com/blogs/kalen_delaney/archive/2010/03/07/more-about-nonclustered-index-keys.aspx) – Martin Smith Sep 07 '12 at 18:03
0

I believe this has to do with the row locator.

Width of the nonclustered index row = Width of the nonclustered index column + Width of the clustered index column = size of column data type + size of column data type.

Kermit
  • 33,827
  • 13
  • 85
  • 121