When I was reading SQL Server 2008 Internal, I was attracted by one statement which was “Clustered index is always be unique. If not, SQL Server will add one 4 bytes uniquifier to enforce the uniqueness.” I was wondering how SQL Server would use this 4 bytes, be rotate or otherwise. Because the rows of one table will be easy to exceed the max value of 4 bytes, which is just 4.2 billion. Then I raised up a thread in SQL Server MVP mails group. Very surprised I got two replies from Denny and Gail in just few minutes. Seems almost be 2 to 3 years ago they already thought about this question. So shamed I am. I waste too much time on WOW these years. 🙂
Anyway, I still decided to test it out with DBCC commands. Because the value of uniquifier was hidden from normal query.
I use the script here to test.
create database test
go
create table test
(
name nvarchar(100) not null,
value nvarchar(100) not null
)
go
create clustered index cx_test on test(name)
go
insert into test values (‘ryan’, ‘100000’)
insert into test values (‘quentin’, ‘20000’)
insert into test values (‘ryan’, ‘100001’)
insert into test values (‘quentin’, ‘20001’)
go
–after this statement, I will know which page I need to dump
DBCC IND(‘test’, ‘test’, 1)
go
–the result in my machine shows data page number is 55
DBCC PAGE (test, 1, 55, 3)
In the result, finally I confirmed replies from Denny and Gail. The uniquifier values allocated in the scope of duplicate group separately, each one start at 0. So two rows of ‘ryan’ will get 0 and1, ‘quentin’ also get 0 and 1. Then no problem for the out range of 4 bytes. As Gail said, it will really be evil if someone was planning to insert over 4.2 billion duplicated values into one table. But I still thought this was a little expensive to maintain uniqueness.
BTW, I also found another secret, maybe not. 🙂 SQL Server allocates the uniquifier value as identity, which means the uniquifier value was continuously increasing and seems never reuse the value if some rows were deleted. Just simply delete some records and insert them again, you can easily find it with the same DBCC command.
drillchina 