Use the uniqueidentifier Data Type

Use the uniqueidentifier data type for the ID column, and use the NEWID() function to generate an ID value for each new row.

The advantages of doing this are:

• The GUID is guaranteed to be globally unique. You can completely avoid conflicts by using the GUID.
• You cannot exhaust the list of GUIDs, as you could ranged identities.

This method is fine within the context of Microsoft® SQL Server™ when users will not see or use the GUID values, and they are always guaranteed to be globally unique. They are used extensively within the internal storage of replication information.

The disadvantages of using the uniqueidentifier data type arise when the ID values are used outside of SQL Server:

• uniqueidentifier values in the form:

  ‘6738F4BB-4B08-11D1-853F-00C04FBFE9AF’

  are cumbersome for users who need to enter them for queries.

• uniqueidentifier values are random and have no other meaning; users and database designers like to have incrementally increasing ID values because they can give a general indication of the number of rows in the table.
• uniqueidentifier values may not be an option for existing applications that are built around incremental ID values.

Combining an ID Value with Another Value

A second alternative is to combine the ID value with another unique value in the database or on the server. By definition, the identifier for a table should be declared with a PRIMARY KEY or UNIQUE constraint. You can create the constraint on a compound key that includes a standard “ID” value and another value that makes the compound ID unique in each server/database. For example:

CREATE TABLE sales

(sale_id INT IDENTITY,

sales_region CHAR(2),

CONSTRAINT sales_pk PRIMARY KEY (sale_id, sales_region)

.

.

)

In this case, the same sale_id value can be reliably used across several servers as long as the same sales_region value is not used in two different servers. The disadvantage of this method is that the sequential ID values may be duplicated across servers; they cannot be used alone in queries without the accompanying value that makes the ID value unique.

See Also

uniqueidentifier