Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

NEWSEQUENTIALID() and NEWID() both generates the GUID of datatype of uniqueidentifier. NEWID() generates the GUID in random order whereas NEWSEQUENTIALID() generates the GUID in sequential order.

Example:

CREATE TABLE ##TestTable

(NewIDCol uniqueidentifier DEFAULT NEWID(),

NewSeqCol uniqueidentifier DEFAULT NewSequentialID())

----Inserting five default values in table

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

INSERT INTO ##TestTable DEFAULT

VALUES

----Test Table to see NewID() is random

----Test Table to see NewSequentialID() is Incremental Sequence

SELECT *

FROM ##TestTable

----Clean up database with droping column

--DROP TABLE ##TestTable

GO

Output:

Function NEWSEQUENTIALID() can not be used in SQL queries and it can be only used in DEFAULT clause of table. NEWSEQUENTIALID() are predictable, in case of privacy or security use NEWID() instead of NEWSEQUENTIALID(). If tried to use in QUERY it will thrown an error. NEWID() will work perfectly fine when used in queries.

Example:
----This will thrown an errorSELECT NEWSEQUENTIALID()GO

ResultSet:
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.
----This will return GUIDSELECT NEWID()GO
ResultSet:
————————————
26CE6817-B138-413A-92AD-A2F2BBF7E0B8

Additional Information from Book On-Line

The GUIDs generated by NEWSEQUENTIALID() are unique only within a particular computer if the computer does not have a network card.

You can use NEWSEQUENTIALID() to generate GUIDs to reduce page contention at the leaf level of indexes.

The value returned by NEWID is different for each computer.