Technical Article

Unique Identifier as Clustered index

,

As all DBAs should know, a unique identifier as a clustered index is not an index you wish to have in your database. This script detects such index. It is a good start to investigate your database design.

Copy paste T-SQL code and run it in your favorite database.

The subquery (with alias B) selects the indexes with only one column

/*
    Clustered indexes with only a uniqueidentifier as column
    version 2013-10-01
    by Wilfred van Dijk (wfvdijk@gmail.com)
*/select  object_name(a.object_id), a.name,  d.name
from    sys.indexes a
join    (   select object_id, index_id
            from sys.index_columns ic
            group by object_id, index_id
            having max(index_column_id) = 1
        ) b
on      a.object_id = b.object_id
and     a.index_id = b.index_id
join    sys.index_columns bb
on      b.object_id = bb.object_id
and     b.index_id = bb.index_id
join    sys.columns  c
on      bb.object_id = c.object_id
and     bb.column_id = c.column_id
join    sys.types d
on      c.user_type_id = d.user_type_id
where   a.object_id > 1000 -- no system objects
and     a.type = 1 -- clustered
and     c.user_type_id = 36 
order   by 1,2;

Rate

4 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (5)

You rated this post out of 5. Change rating