composite primary key using newid and newsequentialid

Question

I know that using GUID generated by newid isn't a good candidate for primary key for performance issues.

How about a composite primary key with {newsequentialid(), newid()} so a new GUID is guranteed to be greater than the one generated previously?

Is there a performance issue here too?

You may think why would anyone do this but i am writing a code analysis rule and don't know what crazy things that users will do :)

Thanks

Answer 1

This is even worse than a straight, single GUID created by using just newid().

Why??

• it's still totally random (because of the newid() part) thus causing massive index fragmentation
• it's twice as large as a single GUID, making all your non-clustered indices more bloated and less efficient (32 bytes vs. 4 bytes for an INT IDENTITY)

So I would recommend to either:

• stick with just a newsequentialid() as the default for your PK, if you really want and need a GUID-style PK
• use a INT/BIGINT IDENTITY for optimal performance (if replication is not a requirement)

That's the best two choices you have. See Kimberly Tripp's excellent articles on why a random GUID is a really really bad choice for a clustering key:

• GUIDs as PRIMARY KEYs and/or the clustering key
• The Clustered Index Debate Continues...
• Ever-increasing clustering key - the Clustered Index Debate..........again!