SQL Server Performance

making a non-unique clustered index unique

Discussion in 'T-SQL Performance Tuning for Developers' started by B@ns, Oct 18, 2006.

  1. B@ns New Member

    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
  2. Adriaan New Member

    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.
  3. FrankKalis Moderator

    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
  4. Adriaan New Member

    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.
  5. Roji. P. Thomas New Member

    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
  6. Roji. P. Thomas New Member

    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
  7. FrankKalis Moderator

    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
  8. B@ns New Member

    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
  9. Roji. P. Thomas New Member

    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
  10. B@ns New Member

    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
  11. Roji. P. Thomas New Member

    quote:Originally posted by B@ns

    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
    No. I meant non-clustered. Clustered index by itself, covers all the columns.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  12. B@ns New Member

    Thanks Roji!

    So, it means that if I add a column to a clustered index, it becomes non-clustered?

    ~Avnip
  13. FrankKalis Moderator

    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
  14. FrankKalis Moderator

    quote:Originally posted by B@ns

    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
  15. B@ns New Member

    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
  16. B@ns New Member

    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
  17. FrankKalis Moderator

    It's better to start a new thread. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  18. B@ns New Member

    Hi!

    I have posted a new thread for this...

    ~Avnip

Share This Page