Menu

SQL Server UNIQUEIDENTIFIER Data Type

Updated on

SQL Server uniqueidentifier stores a 16-byte GUID. Its familiar text form has 36 characters, including hyphens. Use it when an identifier needs to be generated independently or shared across systems; it is not a date, a sequential number, or a secret token. See Microsoft’s uniqueidentifier reference.

Define and generate a GUID

You can insert a valid GUID string, call NEWID() in a statement, or use a default constraint to generate a value when a row is inserted:

CREATE TABLE dbo.Customers (
    CustomerId uniqueidentifier NOT NULL
        CONSTRAINT DF_Customers_CustomerId DEFAULT NEWID(),
    CustomerName nvarchar(100) NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)
);

INSERT INTO dbo.Customers (CustomerName)
OUTPUT inserted.CustomerId, inserted.CustomerName
VALUES (N'Ada Lovelace');

NEWID() can also be called directly to generate a value in a query. NEWSEQUENTIALID() is restricted to a column DEFAULT constraint. It can reduce random page activity when GUIDs are index keys, but its values are more predictable; do not expose them as authorization or session secrets. Compare the NEWID() and NEWSEQUENTIALID() references before choosing a default.

Compare GUID values carefully

SQL Server supports =, <>, <, >, <=, and >= comparisons for uniqueidentifier. Its ordering is not a comparison of the GUIDs’ raw bit patterns, so do not treat ORDER BY as chronological or lexicographic order. The type does not support arithmetic operators.

uniqueidentifier values can be primary keys, but they take 16 bytes and random NEWID() values can cause less sequential index access than integer or sequential GUID keys. Choose a key based on whether identifiers must be generated outside the database, index behavior, storage, and privacy requirements; a GUID’s uniqueness does not make it a security token.

Validate text before conversion

When converting character data to uniqueidentifier, SQL Server applies character conversion truncation rules. A string longer than 36 characters can therefore be truncated before conversion, allowing a valid GUID prefix followed by extra characters to be accepted. Validate the complete input format and length before converting untrusted or imported values; do not rely on conversion alone to reject trailing text. Microsoft’s uniqueidentifier conversion notes document this behavior.

Further reading