October 7, 2013 at 12:40 am
Comments posted to this topic are about the item Unique Identifier as Clustered index
Wilfred
The best things in life are the simple things
October 22, 2013 at 6:35 pm
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
October 23, 2013 at 1:55 am
Good addition, thanks for your reply!
Wilfred
The best things in life are the simple things
October 28, 2013 at 1:00 pm
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?
October 28, 2013 at 1:51 pm
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
April 27, 2016 at 2:02 pm
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