Menu

SQL Server NEWID() Function

Updated on

NEWID() creates a value of the uniqueidentifier type. A uniqueidentifier is a 16-byte GUID, useful when an ID must be generated in SQL Server or across multiple systems.

Generate a GUID for a table row

Set NEWID() as a column default so SQL Server generates 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');

You can also assign the value to a variable:

DECLARE @customer_id uniqueidentifier = NEWID();
SELECT @customer_id AS customer_id;

Randomly select a row

ORDER BY NEWID() can randomize a result set:

SELECT TOP (1) ProductID, Name
FROM Production.Product
ORDER BY NEWID();

This sorts the candidate rows by generated GUID values. For a large table, sorting all rows can be expensive; use a sampling method suited to the table size when performance matters.

NEWID() and NEWSEQUENTIALID()

NEWID() returns a non-sequential GUID. For a uniqueidentifier column used as an index key, SQL Server’s NEWSEQUENTIALID() can improve index locality by generating sequentially ordered values. It can be used only in a column DEFAULT constraint, not as an expression in a query. Sequential GUIDs can also reveal information about generated values, so do not use them as unguessable security tokens.

A GUID’s uniqueness does not make it a cryptographically secure password, session ID, or access token. Do not use NEWID() for secrets. SQL Server’s CRYPT_GEN_RANDOM() generates cryptographic random bytes; applications should use an appropriate password-hashing or token-generation design for their security requirements.

Further reading