SQL Server Performance Forum – Threads Archive

making a non-unique clustered index unique

Hi All! In what circumstances should we consider adding a composite column to a non-unique clustered index in order to make the index unique, rather than letting the SQL Server database engine add an uniquefier? Thanks in advance. ~Avnip

Adding a "composite column" to a non-unique clustered index – I wonder what you’re thinking? When you have a clustered index on multiple columns, it is often suggested that you should change this to a non-clustered index. You then add a column "with identity" with a clustered unique index all to itself, preferably as the primary key, and rebuild the other index(es). If you would add the column with identity (and its own unique constraint) to the existing index, indeed it would automatically make all entries on the index unique – but that is because the new column has unique values only. You would still have to change the index into a unique index – but again, it would only be the identity column that makes it unique, and the other columns will not be unique by themselves. The uniqifier only comes into play when …. not really sure, but I think it’s when you do not have both a primary key and a clustered index defined on the table, and you do have a non-clustered index.

The uniquifier is only added to duplicate keys of a clustered index, because internally clustered index are required to be unique. It is not added to each and every index key. Considering adding another column to the index just to avoid this uniquifier doesn’t seem to be a good idea. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Frank, I wonder whether the uniquifier is only added in case there is no unique constraint or index defined on the table … BOL seems to be silent on this subject.

quote:Originally posted by FrankKalis The uniquifier is only added to duplicate keys of a clustered index, because internally clustered index are required to be unique.

Frank, I guess even if the uniquefier is not addded to every row in the clustered index, the space overhead is 4 bytes per every row. Roji. P. Thomas
http://toponewithties.blogspot.com

quote:Originally posted by Adriaan Frank, I wonder whether the uniquifier is only added in case there is no unique constraint or index defined on the table … BOL seems to be silent on this subject.

Adrian,
AFAIK, An integer uniquefier is added to the clustered index key when the clustered index key is not unique by itself. And the clustered index key is added to every non-clustered, non-unique index when the clustered index key is not explicitly part of the index. Roji. P. Thomas
http://toponewithties.blogspot.com

Hm, I must correct myself.http://support.microsoft.com/default.aspx?scid=kb;en-us;304519

quote:
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.

—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Thanks Adriaan/Roji/Frank! Well, as Roji pointed out that 4 bytes for every row are taken up by the uniquefier, can there be a reason/situation wherein we add another column to the non-unique clustered index rather than letting the database engine add the uniquefier? Say, if the column that has been added to the composite index is very narrow – e.g. an identity column? Again, Frank has already mentioned that its not a good idea to do so, but, I am still wondering if there can be any such need/requirement OR can we just say that – "Always let the engine add the uniquefier rather than adding an extra column to the non-unique clustered index to make it unique"?? Thanks again guys. ~Avnip

I’d recommend adding a column to the clustered index to make it unique, instead of letting SQL server add a uniquefier, because it makes your non-clustered index cover the additional column you added. Roji. P. Thomas
http://toponewithties.blogspot.com

Hi Roji, Just wanted to make sure if you meant "clustered" index there instead of "non-clustered"…

quote:I’d recommend adding a column to the clustered index to make it unique, instead of letting SQL server add a uniquefier, because it makes your non-clustered index cover the additional column you added.

Thanks and Regards,
Avnip

quote:Originally posted by [email protected] Hi Roji, Just wanted to make sure if you meant "clustered" index there instead of "non-clustered"…

Thanks and Regards,
Avnip

No. I meant non-clustered. Clustered index by itself, covers all the columns. Roji. P. Thomas
http://toponewithties.blogspot.com

Thanks Roji! So, it means that if I add a column to a clustered index, it becomes non-clustered? ~Avnip

Hm, rethinking it, it might be that adding an INT column to the clustered index turns out to be smarter than rather let SQL Server handle this by adding the uniquifier. But if that INT column is an IDENTITY column by itself, it might be even smarter putting the clustered index on that column alone. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

quote:Originally posted by [email protected] Thanks Roji! So, it means that if I add a column to a clustered index, it becomes non-clustered? ~Avnip

No, certainly not. It becomes a composite clustered index. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Thanks Frank! Thanks to Roji and Adriaan.
This discussion has been very helpful to me!
I am still tracking it, though always on a look out for more n more info! Best Regards,
Avnip

Hi All! I have got another doubt related to indexes (may be I should start a new thread, but, just in case it’s a short reply) – What is the cost involved in accessing the data pages for SQL Server 2005 in case of tables, which, do not have any clustered index on them? Thanks in advance. ~Avnip

It’s better to start a new thread. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Hi! I have posted a new thread for this… ~Avnip

]]>