Uniqueidentifier vs. IDENTITY Column in SQL Server

For those of you who are not familiar with the uniqueidentifier datatype, here's the lowdown:

1) Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)

2) GUID is always guaranteed to be 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.

3) To get a GUID in SQL Server (7.0+), you call the NEWID() function.

4) The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.

Now, unlike an IDENTITY column, a uniqueidentifier column doesn't automatically gets 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,'Hello World')

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 or in Smart Client which have local data storage in MSDE etc. 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 integers (4 bytes). Also value of an IDENTITY columns cab be retrieved by using @@IDENTITY or SCOPE_IDENTITY().

@@IDENTITY and SCOPE_IDENTITY() will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.