Jeff Moden (11/22/2012)
dave-L (11/22/2012)
Hi Jeff. That's my whole question really. Is there a good way to fix this and is it worth trying to fix.True using a UDF to generate the UUIDs will have more over head than native NEWID(), but my thought was I would save so much time on the scores of thousands of key lookups that occur because my CI is on a useless identity that this would be worth it.
Dave
I'm still confused about it all. I thought you said that the GUID from the GUI was absolutely required and has been used in thousands of places throughout the code. The only way that you'd get rid of the Key Lookups is to do one of two things. Either put the CI on the GUID with a decent FILL FACTOR to prevent rampant page splits during inserts or build a covering index with the GUID as the leading column. All of this other work will do nothing to get rid of the Key Lookups.
I think where things are going is that if the GUID is made to be sequential then the CI can be again redefined using the GUID resulting in the key lookups going away and fragmentation due to mid-page page splits also going away meaning the FILLFACTOR does not need to be modified solely to ease things up for a non-sequential GUID.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato