My latest blog post on Go Daddy’s Insiders site is now live! In it, I discuss query performance and scalability issues with GUIDs. Read the article and let me know what you think.
Clear, concise, simple. Love it. I’ll be pointing developers to this article when asked if GUIDs are a good choice for clustered indexes.
Clear, but the bigint solution will require re-architecting the part of the application that creates keys and will cause some issue when porting data. Beside that if you need to partition your database across multiple servers you’ll have to manage the bigint partitioning as well.
Can you also elaborate on the same scenario (GUID keys) but using sequential guids instead of standard ones?
As many pointed out in the comments to the article, using newsequentialid() instead would help with the fragmentation issue. But a guid is still a guid, taking up lots of space in leaf level of NCIs, so it doesn’t solve all of the space issue (though again, less page splits was probably explaining most of your disk-saving in the 4,5 billion row table example).
Nice article though! There are lots of tables out there having guids as PK, where the guid is created somewhere else than in the database (Entity Framework eg), and then there is no such thing as sequential guid, so clustering on something else than a guid is very relevant anyway.
Thanks for a great, easy-to-understand graphical explaination to external and internal fragmentation!
Your email address will not be published. Required fields are marked *
You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>