In SQL Server, both the NEWSEQUENTIALID() function and the NEWID() function create a GUID (Globally Unique IDentifier), also known as UUID (Universally Unique IDentifier).
A GUID can be used as a unique identifier in columns of type uniqueidentifier, so both functions can be used for that purpose.
However, there are differences between these two functions that may influence your decision to use one over the other.
The Differences
Here are the main differences between these two functions.
| NEWID() | NEWSEQUENTIALID() | |
|---|---|---|
| GUID | Creates a random GUID. | Creates a sequential GUID. |
| Approach | The GUID complies to RFC 4122 version 4, which specifies that the GUID is randomly or pseudo-randomly generated. | Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. |
| Return Type | uniqueidentifier | uniqueidentifier |
| Usage | Can use in ad-hoc queries, tables, variables, etc |
Can only use with DEFAULT constraints on table columns of type uniqueidentifier.
|
| Performance |
Can be slower than NEWSEQUENTIALID(), because NEWID() causes random activity and uses fewer cached data pages.
|
Can be faster than NEWID(), because NEWID causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID() also helps to completely fill the data and index pages.
|
| Security | More secure, as the GUID is randomly generated, and harder to guess. | Less secure. It’s possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID. |
I’m sure there are plenty of other differences under the hood, but these are the main differences from a user’s perspective.
Example 1 – Comparing the GUIDs
Here’s a quick example to demonstrate the difference in the GUID that each of these functions produce.
CREATE TABLE GUIDTest
(
NewIdCol uniqueidentifier NOT NULL DEFAULT NEWID(),
NewSequentialIdCol uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
);
GO
INSERT GUIDTest (NewIdCol, NewSequentialIdCol)
VALUES (DEFAULT, DEFAULT);
GO 20
SELECT
NewIdCol AS [NEWID()],
NewSequentialIdCol AS [NEWSEQUENTIALID()]
FROM GUIDTest;
GO
Result:
+--------------------------------------+--------------------------------------+ | NEWID() | NEWSEQUENTIALID() | |--------------------------------------+--------------------------------------| | 965320de-8997-4e37-8fe9-a7daa52b04aa | 246f433e-f36b-1410-8a80-007d2b533547 | | 5c038bef-c2e1-4558-bd91-49b98fde5c71 | 266f433e-f36b-1410-8a80-007d2b533547 | | e7cfba90-78b6-4360-87a8-0b392fd227e5 | 286f433e-f36b-1410-8a80-007d2b533547 | | 9bf5b052-7023-4f52-939e-b5fb8f8ea27a | 2a6f433e-f36b-1410-8a80-007d2b533547 | | ccb38891-3689-42ed-91f2-79ddf0abeb2f | 2c6f433e-f36b-1410-8a80-007d2b533547 | | 8646ab08-6e4b-4cd6-9b25-ec0440c0f7f3 | 2e6f433e-f36b-1410-8a80-007d2b533547 | | f14e18a5-669a-4a2d-8793-42b705fc134f | 306f433e-f36b-1410-8a80-007d2b533547 | | 09ec2418-cfa0-4c8b-9b08-78e6f7946488 | 326f433e-f36b-1410-8a80-007d2b533547 | | 3ca2b71c-5f84-4db9-aa0f-fb9f215ceb09 | 346f433e-f36b-1410-8a80-007d2b533547 | | ccaed0a6-5851-472d-8b6a-db29581f2a43 | 366f433e-f36b-1410-8a80-007d2b533547 | | be6f9d97-7c86-4e43-9127-1aaa0c75d8b5 | 386f433e-f36b-1410-8a80-007d2b533547 | | b4c7b9e9-6f0c-44fc-904b-1aae2dfd20f9 | 3a6f433e-f36b-1410-8a80-007d2b533547 | | 378b2be8-b368-462f-ad30-896b67974b11 | 3c6f433e-f36b-1410-8a80-007d2b533547 | | 390c58ce-8d5c-454b-8c80-e0a690f113b9 | 3e6f433e-f36b-1410-8a80-007d2b533547 | | 4d2d9c64-bf04-44d2-bee3-4a2a33da05bd | 406f433e-f36b-1410-8a80-007d2b533547 | | 87e73722-bc1c-43f1-b94d-c89053bdc408 | 426f433e-f36b-1410-8a80-007d2b533547 | | 2caf72a0-4a19-4743-b299-99610d44aab2 | 446f433e-f36b-1410-8a80-007d2b533547 | | 3ed0dd28-19d5-44f5-90dc-6ded08269b37 | 466f433e-f36b-1410-8a80-007d2b533547 | | ce768fd8-df02-4d8f-afd4-a62d04571713 | 486f433e-f36b-1410-8a80-007d2b533547 | | 6ca41865-bbee-418a-aa6d-582a4541119d | 4a6f433e-f36b-1410-8a80-007d2b533547 | +--------------------------------------+--------------------------------------+
We can see that the NEWSEQUENTIALID() column is incrementing in a sequential fashion, whereas the NEWID() column appears to be random.
Both columns contain GUIDs and they are are valid uniqueidentifier types.
On the NEWID() column, we can see that all values are RFC 4122 version/(sub-type) 4, which specifies that the GUID is randomly or pseudo-randomly generated. We know it’s version 4 because the 4 is in the relevant spot (all rows take the form: xxxxxxxx-xxxx-4xxx-xxxx-xxxxxxxxxxxx).
Example 2 – Usage in Queries
As mentioned, only NEWID() can be used in queries.
For example, you can do this:
SELECT NEWID() AS [NEWID()];
Result:
+--------------------------------------+ | NEWID() | |--------------------------------------| | ba07340c-6308-44d0-8c4b-52ed5e4a1601 | +--------------------------------------+
But you can’t do this:
SELECT NEWSEQUENTIALID() AS [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.