Advantages and disadvantages of GUID / UUID database keys

Advantages and disadvantages of GUID / UUID database keys 

Advantages and disadvantages of GUID / UUID database keys 




Unique identifiers are one of the fundamental blocks of data modeling and well as data consumption. Every table modeling starts with a unique business key/identity key/surrogate key, etc. The impact of a unique identifier becomes more evident when the need is to consolidate common business entities for purposes like data warehousing and analytics. If the identifiers are unique within datasets, but not across datasets, this can become a data processing bottleneck.  In the case of merger and acquisition scenarios, data gets consolidated at a massive level across organizations and duplicate identifiers can pose significant challenges. The need is to have unique identifiers that can remain unique within the dataset, across datasets and even across organizations. In this tip, we will understand the mechanism to address the problem in question.


A GUID in SQL Server can generate unique IDs with the least probability of the exact ID being generated and/or used by someone else coincidently.



Advantages:

  • Can generate them offline.
  • Makes replication trivial (as opposed to int's, which makes it REALLY hard)
  • ORM's usually like them
  • Unique across applications. So We can use the Primary Key's from our CMS (guid) in our app (also guid) and know we are NEVER going to get a clash.

Disadvantages:

  • Larger space usage, but space is cheap.
  • Can't order by ID to get the insert order.
  • Can look ugly in a URL, but really, WTF are you doing putting a REAL DB key in a URL!? (This point disputed in comments below)
  • Harder to do manual debugging, but not that hard.

Post a Comment

0 Comments