Uniqueidentifier vs. IDENTITY
By Sean Baird
on 12 September 2000
| 21 Comments
| 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
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.
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
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
), 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
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
s (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.