Using Uniqueidentifier Data

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identification numbers (GUID). A GUID is a binary number that is guaranteed to be unique; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.

A GUID value for a uniqueidentifier column is usually obtained:

• In a Transact-SQL statement, batch, or script by calling the NEWID function.
• In application code by calling an application API function or method that returns a GUID.

The Transact-SQL NEWID function and the application API functions and methods generate new uniqueidentifier values from the identification number of their network card plus a unique number from the CPU clock. Each network card has a unique identification number; network card manufacturers guarantee that no other network card in the next 100 years will have the same number. The uniqueidentifier returned by NEWID is generated using the network card on the server. The uniqueidentifier returned by application API functions and methods is generated using the network card on the client.

A uniqueidentifier is not typically defined as a constant because it is very difficult to ensure that the uniqueidentifier created is actually unique. There are two ways to specify a uniqueidentifier constant:

• Character string format

  '6F9619FF-8B86-D011-B42D-00C04FC964FF'

    

• Binary format

  0xff19966f868b11d0b42d00c04fc964ff

    

The uniqueidentifier data type does not automatically generate new IDs for inserted rows the way the IDENTITY property does. To get new uniqueidentifier values, a table must have a DEFAULT clause specifying the NEWID function, or INSERT statements must use the NEWID function:

CREATE TABLE MyUniqueTable

    (UniqueColumn    UNIQUEIDENTIFIER        DEFAULT NEWID(),

    Characters        VARCHAR(10) )

GO
INSERT INTO MyUniqueTable(Characters) VALUES ('abc')

INSERT INTO MyUniqueTable VALUES (NEWID(), 'def')

GO

uniqueidentifier columns may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified for the column. A foreign key column referencing a uniqueidentifier primary key in another table will have multiple occurrences of individual uniqueidentifier values when there are multiple rows referencing the same primary key in the source table.

A table can have multiple uniqueidentifier columns. One uniqueidentifier column per table may be specified with the ROWGUIDCOL property. The ROWGUIDCOL property indicates that the uniqueidentifier values in the column uniquely identify rows in the table. The property does not do anything to enforce this, however. The uniqueness must be enforced through other mechanisms, such as specifying the PRIMARY KEY constraint for the column. The ROWGUIDCOL property is primarily used by SQL Server replication.

The main advantage of the uniqueidentifier data type is that the values generated by the Transact-SQL NEWID function or the application GUID functions are guaranteed to be unique throughout the world.

The uniqueidentifier data type has several disadvantages:

• The values are long and obscure. This makes them hard for users to always key in correctly, and even harder for users to remember them.
• The values are random and cannot accept any patterns that may make them more meaningful to users.
• There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on serially incrementing key values.
• At 16 bytes, the uniqueidentifier data type is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key.

Consider using the IDENTITY property when global uniqueness is not necessary, or when having a serially incrementing key is desirable.

See Also