All geek questions in one place

Why SQL Server appends a 4-byte integer to non-unique clustered indexes

You can define non-historical columns, both clustered and non-clustered indexes. However, SQL Server adds a 4-byte integer to the indexed columns in the case of a clustered index if the column is not defined as unique. This is done in order to preserve the “uniqueness” of the record inside, although two or more records may be relevant for this column. Why is this integer not necessary in the case of a non-clustered index?

+2
source share
2 answers

A non-clustered index already includes a clustered index column so that it can refer to the exact row with which it relates. Therefore, using the uniquifier in the clustered index, the non-clustered index will also include a unique identifier.

Good explanation here: Understanding and exploring Uniquifier in SQL Server

+6
source

I believe this is due to the string locator.

Width of a non-clustered index row = Width of a non-clustered index column + Width of a clustered index column = size of a column data type + size of a column data type.

0
source

Source: https://habr.com/ru/post/1529935/

More articles:


All Articles