Using UUID/GUIDs as Primary Keys in a Database
My company recently interviewed a potential ColdFusion developer. In the interview, he mentioned somthing about using UUID’s as primary keys. This sounded fairly interesting to me because I personally don’t like the auto-incrementing most database systems have (our Oracle 10g at work reuses keys if you actually DELETE a row which makes debugging hectic) and implementing my own version of auto-incrementing seems a bit like a hack but at least it works well so far:
(SELECT NVL(MAX(MYID), 0)+1 FROM DBNAME.TABLENAME)
After doing some research, however, it seems that using a UUID/GUID is not the most favorable thing to use as a key. Primary keys, by definition, are indexed and when it makes comparisons on multiple objects (like when doing a JOIN) it greatly benefits the database system to have to compare 32 - 64 bit integers depending on the processor other than 32 bit string of random hexadecimal characters. One figure I’ve read is that integer comparison takes 1 cycle to be compared in the CPU, while it may take as many as 32 comparisons for the UUID/GUID, and this doesn’t count storage space as a concern.
It may make sense for your project to use a UUID/GUID as the primary key if your project is small enough and won’t demand the resources of a popular social networking application, but even then I would suggest having your programming environment generate the UUID/GUID because it will make your application much more scaleable and portable if you need to move to another server or database backend.
In my case, I’ll keep continuing to use my auto-incrementing integers while attempting to find creative uses for UUID’s. They seem like too much fun to not be using them.
–
Want to read more on ColdFusion’s UUID implementation and the difference between UUID’s and Microsoft’s GUID’s? Read this Adobe Knowledge Base article (link, adobe.com)!
October 15th, 2008 at 11:20 am
Ramon, IIRC, UUID’s / GUID’s do make for decent primary keys except they are a bit slower because it is a full field search. Also be careful in your implementation, I once took over an e-commerce application that implemented this method and found that the orders were all incorrect because the programmer, after inserting a new client, performed a [select max()] in T-SQL, and of course this returned the wrong record because UUIDS/GUIDS don’t insert in any specific order. So each order was filled for the same customer each time a subsequent customer placed one. The worst, this was live on the internet.
So good to use, but be cautious on implementation and mindful of speed.