Uniqueidentifier vs. IDENTITY

By Sean Baird on 12 September 2000 | Tags: Identity

Kim writes: "with an identity column you can SELECT @@identity after inserting a row. How do you get the value of a uniqueidentifier column after an insert? thanks!"

Uniqueidentifiers are new to SQL 7.0, and behave a little differently than our friend the IDENTITY column. Read on for the answer to Kim's question along with the SQLGuru's opinions on which is better as a key value.

First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:

Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)
That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time. I don't know the full mechanics of creating a GUID, but I seem to remember that it has something to do with the MAC address on your network card and the system time.
To get a GUID in SQL Server (7.0+), you call the NEWID() function.
The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.
This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.

Now, unlike an IDENTITY column, a uniqueidentifier column doesn't automagically get an assigned value when a row is inserted into a table. You either need to place a default on the uniqueidentifier column (DEFAULT NEWID()), or do something like the following:

DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
INSERT Item VALUES (@GUID,'Yak Hoof')

So, to answer Kim's question: Sorry, there isn't a way to get the value of a uniqueidentifier column after an insert. You can get it before the insert, however, by using the above code.

The major advantage of using GUIDs is that they are unique across all space and time. This comes in handy if you're consolidating records from multiple SQL Servers into one table, as in a data warehousing situation. GUIDs are also used heavily by SQL Server replication to keep track of rows when they're spread out among multiple SQL Servers.

The main disadvantage to using GUIDs as key values is that they are BIG. At 16 bytes a pop, they are one of the largest datatypes in SQL Server. Indexes built on GUIDs are going to be larger and slower than indexes built on IDENTITY columns, which are usually ints (4 bytes).

Not only that, but they're just plain hard to read. Unless you need a truly globally unique identifier, you're probably better off sticking with an IDENTITY.

-SQLGuru