what will be the effect if uniqueidentifier data type column is clustered/Non Clustered index in a table sql server 2005/2008. I read it is badly designed table, how to avoidy this problem and what is the best solution?
+1
A:
If it is non-clustered, it just means the index will be wide (16 bytes per row, rather than 4 bytes per row with an integer).
If it is clustered, then insertions will lead to page splits, depending on how much free space (Fill Factor) you leave in the index when you create/rebuild it.
There are a few questions on SO discussing this topic:
Should I get rid of clustered indexes on Guid columns
Advantages and disadvantages of GUID / UUID database keys
Clustered primary key on unique identifier ID column in SQL Server
Mitch Wheat
2009-06-09 03:21:13
A:
GUID is a bad choice for clustered index in SQL Server, since due to its randomness of values, the clustered index gets badly fragmented.
Also, since the clustered index field(s) are replicated into each of the non-clustered indices, it can also lead to significant waste of disk and memory space in SQL Server.
GUID is a great choice from a programmer's perspective - more or less random, almost guaranteed to be unique - but from a database perspective, using them as clustered index in SQL Server is very bad choice.
See Kim Tripp's various articles on this - very englihtnening!
http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
http://sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx
Marc
marc_s
2009-06-09 05:11:36
If space is left (i..e Fill Factor) in a clustered index on a GUID, it is NOT necessarily as bad as is often reported. If you have regular index mainatenance and a carefully thought out Fill Factor. Saying that, I try not to make clustered indexes on GUIDS...
Mitch Wheat
2009-06-09 11:31:33
Yes - you can mitigate the effects with a bit of smarts - but GUIDs as clustered keys are just always less ideal than an INT - period. Also, the wasted space (multiplied by all non-clustered indices which contain the clustering key) cannot be optimized away.....
marc_s
2009-06-09 11:58:49