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

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.


Related Articles

Efficiently Reuse Gaps in an Identity Column (9 February 2010)

How to Insert Values into an Identity Column in SQL Server (6 August 2007)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Understanding Identity Columns (9 March 2002)

Identity and Primary Keys (28 February 2001)

Alternatives to @@IDENTITY in SQL Server 2000 (19 September 2000)

Returning @@IDENTITY back to an ASP Page (18 August 2000)

Other Recent Forum Posts

Using case statement in where clause (12h)

Combine records based on date in between date columns as a continuous single record (1d)

Need Help in SQL query optimization (4d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (4d)

Suspect Parameter Sniffing? (5d)

See values of a proc when it is called from another proc? (5d)

Varchar() datatype and C# SqlDataReader truncates string (6d)

Find the extra rows (6d)

- Advertisement -