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.
Let us see example first demonstrating both of the function.
USE AdventureWorks;
GO
----Create Test Table for with default columns values
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
ResultSet:
NewIDCol NewSeqCol
———————————— ————————————
DC896759-8B6B-4A62-8EC8-970BE2F0F04C 3D540550-2138-DC11-BF85-00123FD0986A
71129CD4-3A25-470A-AE8E-9475379EC6A7 3E540550-2138-DC11-BF85-00123FD0986A
B915CCDB-F480-4D89-ADD7-40D5DDE4FD52 3F540550-2138-DC11-BF85-00123FD0986A
5920DA2E-6CC2-4FE3-9C13-F5155B1923A0 40540550-2138-DC11-BF85-00123FD0986A
6C60BFD9-ACE1-4F74-BB74-99343A3A707D 41540550-2138-DC11-BF85-00123FD0986A
It is clear from example of that NEWSEQUENTIALID() generates GUID in hexadecimal incremental interval. The hexadecimal number can be any placed in GUID. In the example above first two digits are incrementing sequential.
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 error
SELECT 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 GUID
SELECT 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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
Good One
Hi,
What are the performance gain for
uniqueidentifier column with NEWSEQUENTIALID() as DEFAULT VALUE and NON CLUSTURED INDEX
against
uniqueidentifier column with NEWID() as DEFAULT VALUE and NON CLUSTURED INDEX
Thank-you.
How do I retrieve what the new id is? Usualy, I would use scope_itendity() to get what the record’s id is. But scope_identity() data type is integer, not guid. Is there a new scope_identity()-equiv method in SQL?
Jerry,
Instead of relying sql server to insert the identity for you or on scope_identity() to get its corresponding value, just use a temporary variable to hold it and insert it yourself.
Example:
DECLARE @mynewid uniqueidentifier;
— choose one of the following
SET @mynewid = newid()
SET @mynewid = newsequentialid()
INSERT INTO MyTable(@mynewid, ‘other fields’,….)
Hi,
How to get the last inserted NEWSEQUENTIALID()
Senthil
I am using NewId() to retrieve random records ..Is it correct?
May I know how this function works?
with regards,
Ravi Shankar
can i use newid() to bring me top 4 random row in xml explicit and
example
I Tried to create this but without all success if will be able to help me I will be very grateful
this is good.
oh, so good.
Regarding the question how to retrieve the new identifier.
You can use the output clause to insert within a variable table or retrieve it like:
INSERT INTO Table (col1)
OUTPUT Inserted.IdGuid
SELECT ‘Test’ UNION SELECT ‘Test2’
This gives you two rows.
Nice one
Excellent!!!!!
Good
Some key facts are
1. For best performance of inserts, use NEWSEQUENTIALID()
2. NEWSEQUENTIALID is able to completely fill data and index pages
3. You can use NEWID() in Select statement but now NEWSEQUENTIALID()
4. If you are worried about or have a need for privacy, don’t use NEWSEQUENTIALID()
5. NEWID() is RFC4122 compliant
3. You can use NEWID() in Select statement but you can’t use NEWSEQUENTIALID()