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 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

Discuss this article: 21 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

Randomly Assign to Group (3 Replies)

How to add a dropdown to a texbox inside a report. (4 Replies)

C# code to put a dropdown list to excel (0 Replies)

Update Date based on Employee (4 Replies)

need help on Covering Indexes (0 Replies)

Repeat records depending field value (3 Replies)

Dates from weeknumber (3 Replies)

Transforming rows to Columns with PIVOT (2 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -