Universally Unique Identifiers (UUIDs) as Surrogate Keys

January 19, 2010 at 8:30 am · Filed under Software Architecture, SQL Server

Universally Unique Identifiers (UUIDs) make good surrogate keys for a number of reasons:

  • They are, by definition, globally unique, leading to practical benefits such as:
    • Given a UUID, it’s possible to identify the relation/tuple (and even potentially the database/environment) for which it is a key. This property can prove useful in the field when troubleshooting.
    • UUIDs will not clash across your environments (unless you want them to).
  • They are decentralised. By that I mean that any system is capable of generating a valid UUID. They can, for example, be generated by the middle-tier.
  • They are (somewhat) obscure – depending on the version of the algorithm used (usually version 4).
  • UUIDs can be generated offline.

 

UNIQUEIDENTIFIER is the SQL Server type for UUIDs. With SQL Server, there are some practical considerations that you need to weigh up for your scenario:

  • UNIQUEIDENTIFIERs require 16 bytes for storage (compared to 4 bytes for an INTEGER).
  • INSERTs will be randomly distributed if the UUID is generated via NEWID(). See NEWSEQUENTIALID() for an alternative.
  • JOINs and other operators involving UNIQUEIDENTIFIERs will likely perform worse compared to those involving INTEGERs.

 

Sometimes, the logical advantages of using UUIDs as generated keys in your system can outweigh these practical considerations.

Comments are closed.