Systems Engineering and RDBMS

Using GUID as the Primary Key

Posted by decipherinfosys on April 1, 2008

We have blogged before about how to go about choosing between a natural key vs a surrogate key when doing data modeling work.  One of the recent questions that was raised was about using a GUID as a primary key column.  The need arose from the requirement that the values needed to be unique throughout the enterprise and not just in a particular table which is what you get if you choose to go with the Identity scheme (SQL Server or DB2) or a Sequence (in Oracle or DB2).  One can come up with a number range design to implement this but that is fraught with danger and is very cumbersome to maintain.  So, the solution in those situations is using a GUID but do not use it as a primary key – instead create another column as a GUID and use it as an alternate key in the table.  Choosing GUID as a primary key column has a couple of dis-advantages:

  • The values are long and obscure. The values are random and cannot accept any patterns that may make them more meaningful to users.  The storage for these values is more.
  • There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on serially incrementing key values.
  • At 16 bytes, the uniqueidentifier data type (in SQL Server) is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key since it has to traverse a larger number of pages for the joins as each page can fit lesser number of values.  This also means that the joins will be slower.

In such a scenario where enterprise wide uniqueness is needed, one solution would be to use INT (or if needed – BIGINT) data type for the primary key column and implement an alternate key column using the GUID to suffice the need for having a globally unique identifier value.  That way, you have the best of both worlds and will also be able to meet the requirements.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: