Using uniqueidentifier Data
The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; 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. 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 difficult to ensure that the uniqueidentifier created is actually unique. There are two ways to specify a uniqueidentifier constant:
- Character string format
- Binary format
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 multiple rows reference the same primary key in the source table.
A table can have multiple uniqueidentifier columns. One uniqueidentifier column for each 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 difficult for users to type correctly, and more difficult for users to remember.
- 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 incrementing key values serially.
- 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.