Unique Identifier as Clustered index

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

    The best things in life are the simple things

  • 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;

    Code for TallyGenerator

  • Good addition, thanks for your reply!

    The best things in life are the simple things

  • 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?

  • 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/

    The best things in life are the simple things

  • Thanks for the script.

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

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