In SQL Server, you can use the NEWSEQUENTIALID() function to create incremental unique values.

It creates a GUID (Globally Unique IDentifier) that is greater than any GUID previously generated by this function on a specified computer since the operating system was started. After restarting the operating system, the GUID can start again from a lower range, but is still globally unique.

The NEWSEQUENTIALID() function can only be used with DEFAULT constraints on table columns of type uniqueidentifier. Therefore, you can’t just run a query like SELECT NEWSEQUENTIALID() and expect it to work (but you can do that with the NEWID() function).

Example 1 – As a DEFAULT Value

Here’s a quick example to demonstrate how it works:

USE Test;
CREATE TABLE Prisoner 
(
    PrisonerId uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    PrisonerName varchar(70) NOT NULL,
);

INSERT Prisoner (PrisonerName)
VALUES
    ('Jerry Seinfeld'),
    ('George Costanza'),
    ('Elaine Benes');

SELECT * FROM Prisoner;

Result:

+--------------------------------------+-----------------+
| PrisonerId                           | PrisonerName    |
|--------------------------------------+-----------------|
| a46d433e-f36b-1410-8a80-007d2b533547 | Jerry Seinfeld  |
| aa6d433e-f36b-1410-8a80-007d2b533547 | George Costanza |
| b06d433e-f36b-1410-8a80-007d2b533547 | Elaine Benes    |
+--------------------------------------+-----------------+

Note that the GUIDs were generated as part of the DEFAULT constraint on the table. They weren’t explicitly provided in the INSERT statement.

Example 2 – Explicitly Provided in the INSERT Statement

Here’s what happens if you try to use NEWSEQUENTIALID() in your INSERT statement:

INSERT Prisoner (PrisonerId, PrisonerName)
VALUES (NEWSEQUENTIALID(), 'Kramer');

Result:

Msg 302, Level 16, State 0, Line 1
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

So it needs to be part of a DEFAULT constraint (like in the previous example).

To insert the above data, all we need to do is remove the first column from the INSERT operation:

INSERT Prisoner (PrisonerName)
VALUES ('Kramer');

SELECT * FROM Prisoner;

Result:

+--------------------------------------+-----------------+
| PrisonerId                           | PrisonerName    |
|--------------------------------------+-----------------|
| a46d433e-f36b-1410-8a80-007d2b533547 | Jerry Seinfeld  |
| aa6d433e-f36b-1410-8a80-007d2b533547 | George Costanza |
| b06d433e-f36b-1410-8a80-007d2b533547 | Elaine Benes    |
| b76d433e-f36b-1410-8a80-007d2b533547 | Kramer          |
+--------------------------------------+-----------------+

Example 3 – Used in a SELECT Statement

You’ll get the same error if you try to use this function in a basic SELECT statement like this:

SELECT NEWSEQUENTIALID();

Result:

Msg 302, Level 16, State 0, Line 1
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

Security/Privacy

It’s recommended not to use NEWSEQUENTIALID() for sensitive data, because it’s possible to guess the value of the next generated GUID and therefore, access data associated with that GUID.