NEWID vs NEWSEQUENTIALID

Some pointers:

1. NewSequentialID() and NewID() both generates the GUID of datatype of uniqueidentifier.
2. NewID() generates the GUID in random order
3. NewSequentialID() generates the GUID in sequential order.
4. NewSequentialID() can be used ONLY in default clause of a table.
5. NewSequentialID() is easily predictable
6. So if security is a major concern then go for NewID() instead of NewSequentialID().

Example to demonstrate NEWSEQUENTIALID():

Create table #tblDemoSequentialID
(
Column1 uniqueidentifier default NewID(),
Column2 uniqueidentifier default NewSequentialID(),
Fname varchar(30)
)

Pump-in few dummy records:

Insert into #tblDemoSequentialID (Fname) values ('Vadivel')
Insert into #tblDemoSequentialID (Fname) values ('Rajinikanth')
Insert into #tblDemoSequentialID (Fname) values ('Sivaji')

In this query 'Column1' would demonstrate that the 'NEWID' has generated GUID in random fashion. Also 'Column2' would contain GUID in Sequential Order (refer the below screenshot).

Select Fname as [First Name], Column1 as [NewID], Column2 as [NewSequentialID] from #tblDemoSequentialID



Flush the table and proceed to next demo:

Delete from #tblDemoSequentialID

Create another temp table:

Create table #tblDemoTWO
(
LName varchar(20),
Column_SeqID uniqueidentifier default NewSequentialID()
)

Lets insert data into both these tables alternatively. This is to prove that the NewSequentialID would be in sequential order :)

Insert into #tblDemoSequentialID (Fname) values ('VDSI')
Insert into #tblDemoTWO (Lname) values ('Yuvaraj')

Insert into #tblDemoSequentialID (Fname) values ('Verizon')
Insert into #tblDemoTWO (Lname) values ('India')

Insert into #tblDemoSequentialID (Fname) values ('Dhoni')
Insert into #tblDemoTWO (Lname) values ('Mahendra')

Select Fname as [First Name], Column1 as [NewID], Column2 as [NewSequentialID] from #tblDemoSequentialID where [Fname] in ('VDSI', 'Verizon', 'Dhoni')

Select Lname as [Last Name], Column_SeqID as [NewSequentialID] from #tblDemoTWO

Just go through the output of both the above queries. You could find it for yourself that the NewSequentialID columns in both the tables got incremented sequentially.

Clean up :

Drop table #tblDemoSequentialID
Drop table #tblDemoTWO
Go