Unique Identifier as Clustered index

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    Comments posted to this topic are about the item Unique Identifier as Clustered index

    Wilfred
    The best things in life are the simple things

  • Sergiy

    SSC Guru

    Points: 109703

    I believe "indexes with only one column" does not reveal all the indexes we need to see.

    Clustered indexes with 1st column of uniqueidentifier type are as bad, probably even worse.

    Following version of the script reveals all "bad" clustered indexes:

    select object_name(i.object_id) [object name], i.name [index name], c.name [UID column name]

    from sys.indexes i

    inner join sys.index_columns ic ON i.object_id = ic.object_id and i.index_id = ic.index_id AND index_column_id = 1

    inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id

    inner join sys.types t on c.user_type_id = t.user_type_id AND t.name = N'uniqueidentifier'

    where i.object_id > 1000 -- no system objects

    and i.type = 1 -- clustered

    order by 1,2;

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    Good addition, thanks for your reply!

    Wilfred
    The best things in life are the simple things

  • Trevor Ball

    SSCommitted

    Points: 1854

    I'd like to hear an explanation of what the problem is with a clustered index on a uniqueidentifier. Is it just the fact that as the table grows, the index will need to be reorganized often, or is there a more fundamental problem with width or with the datatype itself?

    I have some tables with clustered indexes on uniqueidentifiers, but they are relatively small lookup tables that rarely change, so I haven't been concerned about them up to now; are you saying I should be?

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    I advise you to have a look at http://www.sqlskills.com, which contains some excellent articles about index management.

    A direct answer to your question: http://www.sqlskills.com/blogs/kimberly/ever-increasing-clustering-key-the-clustered-index-debate-again/

    Wilfred
    The best things in life are the simple things

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply