question

Bhupendra99 avatar image
Bhupendra99 asked

clustered Index less known facts

Hi I had read somewhere that if we create a clustered index and store duplicate values in it SQL Server adds to every duplicate instance of a clustering key value a 4-byte integer value called a uniqueifier. This uniqueifier is added everywhere the clustering key is stored . consider below example Customer_id(Cluster Key) Unique identifier 1 null 1 1 1 2 2 null The uniqueifier is NULL for the first instance of each customer_id, and is then populated, in ascending order, for each subsequent row with the same customer_id value My question here is how it gonna help me with record identification using key because if I say select * from customer where customer_id=1 it will give me three records then what is the use of adding this uniqueidentifier with each clustered key
clustered-index
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
Well the logical concept is that you say that you want ALL records where Customerid = 1, not a unique record. If you need unique records in the table, you have to specify a unique identifier and ask for the unique value. If you need to remove duplicates, I'll give you a nice query: ;with cte AS ( SELECT rn = ROW_COUNT() OVER(ORDER BY someColumn), AnotherColumn FROM someTable ) DELETE FROM cte OUTPUT deleted.* WHERE rn >1 The uniquefier column is create to make the record unique, because the non-clustered index has to reference a unique value. > "If the clustered index was not > initially created as unique, SQL > Server internally appends a unique > four byte value to the end of each > index key. The unique four byte value > is required so that each nonclustered > index row contains a unique clustered > index key. For a non-unique clustered > index, this four byte value at the end > of the index key may change during the > index rebuild, and thus all of the > nonclustered index keys must also be > rebuilt. If the user did not specify > that the clustered index was unique, > the expected behavior during a reindex > is for all of the indices to be > rebuilt."
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

unique identifier are not visible to users in sql server My question is if it is not visible in normal select query what is the use of it of adding by sql server
0 Likes 0 ·
It's used only for internal processes, and the worst problem with the uniquefier column is that you can run out of numbers.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Because a clustered index stores the data for the table, in order to separate one row from another, there has to be a mechanism that uniquely identifies each row of data. If you have a non-unique clustered index, the additional column is added in the background (and becomes part of the internal key used by non-clustered indexes, but that's a secondary purpose to this) in order to make that key unique so that you can find each of the rows being stored. Otherwise, how could SQL Server know that any one row was completely separate from any other in order to maintain the data in a consistent state?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.