GUID Primary Keys in SQL

Thu, Jun 18, 2009

Tech Tips

Creating a GUID in Microsoft SQL Server:

select newid();

Creating a GUID in MYSQL:

select uuid(); 

Why use a GUID as a primary key?
The main reason is if you want to guarantee uniqueness between databases and servers.

I have never been a fan of GUIDs. Especially when having to write sql statements for reports. They’re difficult to scan and impossible to sort. It used to be they were non existent and then they gained popularity when trying to store objects in relational databases became a fad. Try writing reports against an object hierarchy that is stored in a relational database. Every other column is a GUID. It’s not fun.

Anyway I had one recent experience that reinforced the need of a GUID in a database. Some production data was unintentionally lost in one table(due to a roll back). This particular table was using GUID’s as a primary key. The table could not be restored since it was already out of date. Instead the table was restored to a copy of the database. This allowed a quick comparison of the two tables and a painless insert via sql. What the GUID did in this case, was guarantee sameness (instead of uniqueness).

I am still going to stick to integers as primary keys but I am now going to add an auto generated GUID column for the sake of backup/restores/import/exports/etc.

Bookmark and Share
, , ,

Comments are closed.