Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unique Identifier as Clustered index Expand / Collapse
Author
Message
Posted Monday, October 07, 2013 12:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:27 AM
Points: 925, Visits: 1,094
Comments posted to this topic are about the item Unique Identifier as Clustered index

Wilfred
The best things in life are the simple things
Post #1502000
Posted Tuesday, October 22, 2013 6:35 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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;

Post #1507438
Posted Wednesday, October 23, 2013 1:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:27 AM
Points: 925, Visits: 1,094
Good addition, thanks for your reply!

Wilfred
The best things in life are the simple things
Post #1507486
Posted Monday, October 28, 2013 1:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:43 PM
Points: 953, Visits: 92
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?
Post #1509091
Posted Monday, October 28, 2013 1:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:27 AM
Points: 925, Visits: 1,094
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
Post #1509103
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse