Search

Friday, November 4, 2011

NEWID vs NEWSEQUENTIALID

Some points:
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 #Table_SequentialID
(
Column1 UNIQUEIDENTIFIER DEFAULT NewID(),
Column2 UNIQUEIDENTIFIER DEFAULT NewSequentialID(),
FirstName VarChar(50)
)

Insert few dummy records:
INSERT INTO #Table_SequentialID (FirstName) VALUES ('Raghav')
INSERT INTO #Table_SequentialID (FirstName) VALUES ('Rachna')
INSERT INTO #Table_SequentialID (FirstName) VALUES ('Satya')
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 FirstName AS [First Name], Column1 AS [NewID], Column2 AS [NewSequentialID] FROM #Table_SequentialID
Flush the table and proceed to next demo:
Delete from #Table_SequentialID
Create another temp table:
CREATE TABLE #TableDemo
(
LastName 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 #Table_SequentialID (FirstName) values ('Raghav')
INSERT INTO #TableDemo (LastName) values ('Singh')
INSERT INTO #Table_SequentialID (FirstName) values ('Rachna')
INSERT INTO #TableDemo (LastName) values ('Sinha')
INSERT INTO #Table_SequentialID (FirstName) values ('Satya')
INSERT INTO #TableDemo (LastName) values ('Sundar')

SELECT FirstName AS [First Name], Column1 AS [NewID], Column2 AS [NewSequentialID] FROM #Table_SequentialID WHERE [FirstName] IN ('Raghav', 'Rachna', 'Satya')

SELECT LastName AS [Last Name], Column_SeqID AS [NewSequentialID] FROM #TableDemo
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 #Table_SequentialID
DROP TABLE #TableDemo
Go

No comments:

Post a Comment