Jeff Moden (11/17/2012)
You might be able to fix this. I haven't tested it but it might work.Make the clustered index on the IDENTITY and the GUID column and then change the criteria in the queries to be where SomeIdentityColumn > 0 AND SomeGuidColumn = SomeGuidValue (or whatever). It's a nasty hack but might work.
The advantage here is that the IDENTITY column is first in the index and would virtually eliminate page splits. The change in criteria I mention would cause the clustered index to still be used thereby eliminating the lookups even when you lookup a GUID.
Like I said, not sure it'll work but it seems very likely that it would.
That should certainly eliminate the fragmentation, but wouldn't it always produce a CI can rather than a CI seek? Is that really less damaging than the fragmentation caused by just using the GUIDs?
Tom