SQL Server NEWSEQUENTIALID() Function

In SQL Server, the NEWSEQUENTIALID() function is used to generate a new, non-repeating GUID (globally unique identifier). Unlike NEWID(), the GUIDs generated by the NEWSEQUENTIALID() function are ordered and guaranteed not to have collisions, making it more efficient for building database indexes.

Syntax

The syntax for the NEWSEQUENTIALID() function is very simple, as shown below:

NEWSEQUENTIALID()

Use Cases

The NEWSEQUENTIALID() function is mainly used in the following scenarios:

• As a primary key or unique identifier for database tables, it can replace auto-increment fields.
• In high-concurrency environments for data insertion operations, it can avoid frequent index fragmentation caused by the randomness of GUIDs.

Examples

Example 1: Creating a Table and Inserting Data

The following example shows how to use the NEWSEQUENTIALID() function to create a table named Person and insert some sample data:

CREATE TABLE Person
(
    Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (Id)
);

INSERT INTO Person (FirstName, LastName, Email)
VALUES ('John', 'Doe', '[email protected]'),
       ('Jane', 'Doe', '[email protected]'),
       ('Bob', 'Smith', '[email protected]')

After executing the script above, we can view the data in the Person table, as shown below:

As we can see, each inserted record uses the GUID generated by the NEWSEQUENTIALID() function as the primary key.

Example 2: Using NEWSEQUENTIALID() as a Primary Key

If you need to generate a unique primary key for a table and want to insert records in chronological order, then the NEWSEQUENTIALID() function is very suitable. Here is an example that shows how to create a table with a unique primary key using the NEWSEQUENTIALID() function and insert records:

CREATE TABLE Employee (
   EmployeeID uniqueidentifier DEFAULT NEWSEQUENTIALID(),
   FirstName varchar(50),
   LastName varchar(50),
   Email varchar(50),
   HireDate date
);

INSERT INTO Employee (FirstName, LastName, Email, HireDate)
VALUES ('John', 'Doe', '[email protected]', '2022-01-01'),
       ('Jane', 'Doe', '[email protected]', '2022-01-15'),
       ('Bob', 'Smith', '[email protected]', '2022-02-01');

In this example, we create a table named Employee and use the NEWSEQUENTIALID() function to generate a unique primary key for the EmployeeID column. When inserting records, we do not specify any value for the EmployeeID column because it will automatically use the NEWSEQUENTIALID() function to generate a unique value. When querying the Employee table, you will notice that the values in the EmployeeID column are sorted in chronological order.

Conclusion

The NEWSEQUENTIALID() function is a very useful function that can generate unique GUID values for your tables in chronological order. This makes it a great choice for use as a primary key, especially in situations with high write loads. However, please note that the NEWSEQUENTIALID() function is only supported on the Windows platform. If you need similar functionality on non-Windows platforms, you may want to consider using other GUID generators, such as UUID-OSSP.