Compartilhar via

Using uniqueidentifier Data

  • Artigo

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 by one of the following ways:

  • 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 value that is returned by NEWID is generated by using the network card on the server. The uniqueidentifier value returned by application API functions and methods is generated by using the network card on the client.

A uniqueidentifier value is not typically defined as a constant. You can specify a uniqueidentifier constant in the following ways:

  • Character string format: '6F9619FF-8B86-D011-B42D-00C04FC964FF'
  • Binary format: 0xff19966f868b11d0b42d00c04fc964ff

The uniqueidentifier data type does not automatically generate new IDs for inserted rows in the way the IDENTITY property does. For example, to obtain new uniqueidentifier values, a table must have a DEFAULT clause specifying the NEWID or NEWSEQUENTIALID 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

Note

You can use NEWSEQUENTIALID to generate GUIDs to reduce page contention at the leaf level of indexes. NEWSEQUENTIALID can be used only with DEFAULT constraints on table columns of type uniqueidentifier.

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 that references 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. However, the property does not do anything to enforce this. The uniqueness must be enforced by using other mechanisms, such as specifying the PRIMARY KEY constraint for the column. The ROWGUIDCOL property is primarily used by Microsoft SQL Server 2005 replication. Merge replication and transactional replication that have updating subscriptions use uniqueidentifier columns to make sure that rows are uniquely identified across multiple copies of the table.

The uniqueidentifier data type has the following 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 larger than other data types, such as 4-byte integers. This means indexes that are built using uniqueidentifier keys might be relatively slower than indexes using an int key.

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

See Also

Other Resources

Data Types (Transact-SQL)
NEWID (Transact-SQL)
NEWSEQUENTIALID()
Merge Replication Overview
uniqueidentifier (Transact-SQL)
Updatable Subscriptions for Transactional Replication

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added a note about the NEWSEQUENTIALID function.