Please check out my new site: http://ja.mesbrown.com/

How Unique is a UUID? Should we use them instead of auto-incrementing?

Posted At : November 10, 2010 4:01 PM | Posted By : James Brown
Related Categories: Database, Programming

We had a discussion today about the uniqueness of UUIDs (or GUID). In case you didn't know, according to Wikipedia, a UUID is:

"an identifier standard used in software construction, standardized by the Open Software Foundation (OSF) as part of the Distributed Computing Environment (DCE)."

The uniqueness of a UUID is 1 2^¹²⁸. Now this doesn't really mean much to most people so the part I found particularly interesting is the uniqueness in terms we can try to understand. From Stackoverflow:

"consider the observable universe, which contains about 5×10^22 stars; every star could then have 6.8×10^15 universally unique GUIDs."

Wow.

Here's another way to write the probability:

1 in 340,000,000,000,000,000,000,000,000,000,000,000,000

"In other words, only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%. The probability of one duplicate would be about 50% if every person on earth owns 600 million UUIDs."

Stop and think about that for a second.

This raises the question. Given the fact they basically are unique, should we use them for a primary key instead of auto-incrementing in a database? There are good reasons to use either as a primary key.

Which do you use and why?

Comments (16) |

Print |

Send | 4290 Views |

TweetBacks

Comments

[Add Comment] [Subscribe to Comments]

One good reason to stick with using auto-incrementing integer values is that indexes created based on them performs better.

# Posted By Per Djurner | 11/10/10 4:49 PM

i find using auto-incrementing integer better too

# Posted By Ed | 11/10/10 4:53 PM

Why would you not use auto-increment?

# Posted By brian | 11/10/10 5:22 PM

@brian: I didn't use it in many of my OS apps as I need a nice, cross-DB platform way to insert data and get the ID back. But I wouldn't use it in production to replace a PK.

# Posted By Raymond Camden | 11/10/10 5:24 PM

@brian

Check out this link:
http://www.codinghorror.com/blog/2007/03/primary-k...

or

http://databases.aspfaq.com/database/what-should-i...

Also, a use case would be if you had distributed systems creating primary keys and later wanted to combine the data.

I'm not advocating one over the other yet. Both are options.

# Posted By James Brown | 11/10/10 5:38 PM

I like UUID a lot better. It gives every single record in your database a truly unique ID.
Has anyone not accidentally deleted records from the wrong table? As illustrated above, that's galactically impossible with UUID.

# Posted By Jules Gravinese | 11/10/10 7:40 PM

if you mean use cf to create UUIDs & store them as varchar in the db then no, performance will go in the toilet compared to identity columns. jochem has a bunch of interesting & fact-filled posts on UUIDs & cf: http://jochem.vandieten.net/?s=UUID

# Posted By PaulH | 11/10/10 8:55 PM

@PaulH Thanks for the link. Great information

# Posted By James Brown | 11/10/10 9:42 PM

You asked:
"Given the fact they basically are unique, should we use them for a primary key instead of auto-incrementing in a database?"

The simple answer is, maybe. My question would be what problem are you trying to solve? As far as auto increment "if it ain't broke..", it looks like generating the UUID before each insert is a slight bit more work, as well as a performance hit.

I've been bitten myself many times adding extra steps to an application cause it was new and cool (to me), when it wasn't really necessary.

Just my thoughts.

# Posted By Tim Leach | 11/11/10 10:20 AM

I mix! As many mentioned already, an UUID makes you record key really unique...let's say a product or an articel this has many advantages.... for not 'so' important data like categories or in general all option-tables, I still use auto-increment...or integer since they index faster and look better (shorter) in the url

# Posted By Daniel | 11/11/10 12:00 PM

Why not combine the two? Best of both words.

# Posted By Kokoro Kekere | 11/11/10 9:11 PM

There is a chance, no matter how remote, that the next UUID you generate is identical to the previous one. In incrementing, that chance is 0. The definition of probability is that it could happen and here is your chances of it happening, and the question becomes do you want to take your chances. You cannot equate "probable" with "impossible".

# Posted By Fadi Kouri | 11/12/10 12:08 PM

I think of uuid as a sku. You can have a pile (table) of shirts, and another pile of pants. Each item has a unique sku (uuid) regardless, even if they were all jumbled up together. It may just be philosophy, but I find much better/cleaner record keeping that way.

# Posted By Jules Gravinese | 11/12/10 12:41 PM

@Fadi You probably have about the same chance of a cosmic ray hitting your ram chip and altering a bit to cause your auto-increment increment incorrectly.

# Posted By James Brown | 11/12/10 1:36 PM

if you use a primary key uuid, then you are guaranteed to have a unique key, and then you can use the uuid for passing for transactions or in sessions so that they can't modify other records.

# Posted By Chris Dawes | 11/14/10 11:09 PM

I think there is a fit for both cases. What do you think about in applications where data such as a customer id is passed in the URL? As an extra level of security in cases where you need to pass the customer id through the URL, you could pass the customer uuid instead of an auto-incrementor. This would prevent a user from being able to modify the url parameter of say customer_id=2 to a customer_id=3. Using a uuid, a customer/hacker will have a 1 in 340,000,000,000,000,000,000,000,000,000,000,000,000 of guessing a correct uuid.

# Posted By Tom | 1/16/11 7:43 PM

[Add Comment]

Archives by Subject

Adobe (13) [RSS]
ColdBox (3) [RSS]
ColdFusion (66) [RSS]
Database (4) [RSS]
Facebook (1) [RSS]
Fusebox (2) [RSS]
Google (2) [RSS]
JavaScript (12) [RSS]
jQuery (7) [RSS]
Linux (1) [RSS]
ORM (5) [RSS]
Programming (6) [RSS]
SEO (2) [RSS]
SVN (1) [RSS]
Tips (4) [RSS]
Tour de ColdFusion (3) [RSS]
Twitter (2) [RSS]

Please check out my new site: http://ja.mesbrown.com/

How Unique is a UUID? Should we use them instead of auto-incrementing?

TweetBacks

Comments

Archives by Subject

Recent Entries

Recent Comments

RSS

Tags

ibjhb.com

Recent Comments

Popular Posts

Connect with the Community

Contact Information