GUID in SQL Server

There are many benefits of using GUIDs as the primary key. They are unique across system borders which remove problems with duplicated identities that might occur when working with data from different systems. For example, if the identity of an entity is an integer, two different systems that create the entities will probably have several shared numbers as unique identifiers. This becomes a problem when you, for example, want to merge or replicate data between the systems. The unique constraint will be violated and you have to find a way to work around the problem. During these circumstances, GUIDs are very nice to have because they are unique across system borders.

However, there is a number of drawback also. A GUID requires more resources. It is four times larger than an integer. Which affects the amount of space required to work with GUIDs and also put extra load on I/O and network operations. GUIDs are generated randomly and not sorted. When you generate the next GUID, you don’t know if it is greater or less than previously GUID. Since the GUIDs aren’t generated in an ordered sequence, the index for GUIDs will be fragmented with a high performance penalty. This is not a problem for small databases but a very big problem for larger databases with a lot of inserts.

For system that handles large amount of data, I recommend to avoid GUID and use a sequential id. If it’s really required to have a global unique identity, look at the COMB. COMB is a algorithm to create sequential GUIDs by Jimmy Nilsson.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.