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.
- 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
Lets create table named “T” with a uniqueidentifier data type, and the default value for table column SeqIdCol will be generated by NEWSEQUENTIALID() function when record is inserted in table.
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 you have created a table and inserted few records into table, Lets select records from a table to see whether an Unique Id value is generated for column SeqIdCol or not .
Select * from T
AS you 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 you try to select NEWSEQUENTIALID function in select statement it returns an error as shown below.
Select NEWSEQUNETIALID()
When to use NEWID VS NEWSEQUENTIALID
In case, if you do not want to use random unique number then you should use NEWSEQUENTIALID function.
Whendata security is concerned for you then you should use NEWID function as it 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.
Lets demonstrate this seniaro, first we will create a table named testUniqId and insert some records into table.
Table contains two columns as:
First column named as NewSeqIDCol of data type Uniqueidentifier and NEWSEQUENTIALID function is used to generate default value for this column.
Second column named as NewIdCol of data type Uniqueidentifier and NEWID function is used to generate default value for this column.
CREATE TABLE testUniqId
(
NewSeqIDCol UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
NewIDCol UNIQUEIDENTIFIER DEFAULT NEWID() );
 GO

 INSERT INTO testUniqId (NewSeqIDCol, NewIDCol)
 VALUES (DEFAULT, DEFAULT);
GO 10
As you can see the records in table, you can easily identify the difference between the value generated by NEWID function and NEWSEQUENTIALID function. You can eaisly guess the next sequence number for values which are generated by NEWSEQUENTIALID function while values generated by NEWID function is totally unpredictable.
Therefore, you should choose either of them wisely when data secuitry is concered for you.
SELECT * FROM testuniqId
Also Read..
