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

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?

Answers


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


Need Your Help

Navigating to directory in ZSH (bash)

shell zsh oh-my-zsh

I'm using Oh-My-ZSH to create some ailises and functions for easing my repetitive work load.

hcatalog with mapreduce

hadoop mapreduce hive hcatalog

I get the following error while executing a MapReduce program.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.