GUIDs are bad mkay

A globally unique identifier is a unique reference number used as an identifier in computer software. The term GUID also is used for Microsoft's implementation of the Universally Unique Identifier (UUID) standard.
The value of a GUID is represented as a 32-character hexadecimal string, such as {21EC2020-3AEA-1069-A2DD-08002B30309D}, and is usually stored as a 128-bit integer. The total number of unique keys is 2128 or 3.4×1038. This number is so large that the probability of the same number being generated randomly twice is negligible.
Still, certain techniques have been developed to help ensure that GUID numbers are not duplicated (see Algorithm below). - From Wikipedia/GUID

I have quite often seen the practice of GUIDs being used in databases as primary keys. Much has been written against this practice, but I felt that more voices are louder, so I am also going to chime in. GUIDs are good for a few things:

Making your database incomprehensible

I Once worked on a database that used GUIDs. The biggest problem is that when your inspecting the tables with your eyeballs GUIDs are long scary numbers. You have to do some funky trick where you remember the beginning and the end. If it's a transactional table, then god help you. This will not allow you to write quick selects though, and you will have to copy and paste it everywhere you go. If it is your primary key then your even worse off, because you have to join on them, and you will have a bunch of them lying around in your table structure. It will look all FBI and shit, but that's where the fun ends... REALLY!

Making your database slow

GUIDs as keys are nasty. The DBMS has to work with 32 character hex values instead of ints. Ouch. Indexes on GUIDs then will be huge, and primary keys default to being indexes. So generally a bad idea.

Adding no value whatsoever

Have you ever wondered why your bank account number is not a GUID? The reason is quite simple. You could make a mistake and choose someone else's bank account number. With check digits there is no such problem, because you will have to mess up both specific numbers in the sequence and the check digit at the end. If data integrity and validation is your goal, then check digits are the way to go, not GUIDs

Making your database look like it was written by rainman

An over engineered database looks ugly, works ugly, and is a PITA to maintain because you need to be a savant to keep the mental overhead while your working. GUIds though all unique, also have a nasty quality of looking the same. I know you can level that same argument against normal auto ids, but I'm sure there is an easy way around that. With a GUID appending a character at runtime will not make it more legible. If you are giving users these numbers then you will see that most people are more sand dweebs than rain men when it comes to remembering, repeating, or even copying down extremely long letter/number combinations.


Before you embark on the wonderful journey of GUID think about it carefully. Do you really really really need identifiers that are globally unique? Does that warm feeling of having planned for the most random and unforeseeable eventuality really make it worth the sacrifice?

So what else?

If you're working with a transactional table, you can use normal auto identities. If your auto identities are for something sensitive like bank account numbers, make check digits. If you have a lookup table and want to ensure an identifier for each record make it text based and put a unique key on it like ChecqueAccount, SavingsAccount etc... That will guard you from auto id renumbering and save you the pain of having to look up your lookups so to speak when working with them in code.

Finally end?

I sometimes look at tables using GUIDs and wonder whether there were really good reasons to use them. After close inspection I have not found one good use for them. Identifiers live in a world of context. Your ID number does not make sense when you dial it on your telephone, and your dentist's practice number will not be used as your bank account number when you transfer funds. Human error is a big problem with users especially, something that check digits prevent. GUIDs however seem to make it easier to make a mistake and more difficult to remember the numbers themselves. Nobody is served well by the security. Unless you are using it for security purposes, in which case you just wasted 5 minutes of your life reading this
Post a Comment