SQL Server NEWSEQUENTIALID() function is used to generate incremental unique values of type uniqueidentifier.
NEWSEQUENTIALID() function can only be used with DEFAULT constraints on table columns of type uniqueidentifier.
A return type of NEWSEQUENTIALID() function is uniqueidentifier.
Uniqueidentifier is a Microsoft SQL Server data type that is used to store Globally Unique Identifiers (GUIDs).
It is similar as NEWID() function but there are few difference between them as followings
- NEWID() function generates random unique values while NEWSEQUENTIALID() function generates incremental unique values.
- Performance wise NEWSEQUENTIALID() function can be faster than NEWID() function because NEWID() function causes random activity and uses fewer cached data pages while NEWSEQUENTIALID() function reduce page splits and random IO at the leaf level of indexes.
- Both NEWSEQUENTIALID() function can only be used with DEFAULT constraints on table columns of type uniqueidentifier while NEWID() function can also be used with DEFAULT constraint on table columns and it can also be referenced in queries as well.
Lets look at an example of using NEWSEQUENTIALID() function in SQL Server.
NEWSEQUENTIALID() function with DEFAULT Constraint
We create table with a uniqueidentifier data type, and uses NEWSEQUENTIALID() function to generate the default value for SeqIdCol column.
CREATE TABLE T (SeqIdCol UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),Name varchar(50)) INSERT INTO T (Name) VALUES('Jhony K') INSERT INTO T (Name) VALUES('Henry james')
As we have created a table and inserted few records into table, Lets select records from a table to see whether a Unique Id value generated or not for column SeqidCol.
Select * from T
We can see , unique id value is generated for column SeqIdCol.
Using NEWSEQUENTIALID() function explicitly
NEWSEQUENTIALID() function can not be used explicitly , it can always used with DEFAULT Constraints only.
When we try to select NEWSEQUENTIALID() function in select statement it returns an error saying the it can be used only as default constraint.
When to use NEWID() VS NEWSEQUENTIALID()
In case, if we do not want to use random unique number then we should use NEWSEQUENTIALID() but as far as data security is concerned then we should use NEWID() function as NEWID() function generates a random unique number so it is not possible to guess which number will be the next number but in the case of NEWSEQUENTIALID() function it is quite easy to guess the next number as it could be a just like INTEGER as an auto-increment digits.
CREATE TABLE testUniqId ( NewSeqIDCol UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(), NewIDCol UNIQUEIDENTIFIER DEFAULT NEWID() ); GO INSERT INTO testUniqId (NewSeqIDCol, NewIDCol) VALUES (DEFAULT, DEFAULT); GO 10
when we see the table records, we can easily identify the difference between NEWID() and NEWSEQUENTIALID() function workings.
We should choose one of them wisely in terms of data security.
SELECT * FROM testuniqId