Using GUID as the Primary Key

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.