SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

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 (http://blog.SQLAuthority.com)

14 thoughts on “SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

  1. 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.

    Like

  2. 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?

    Like

  3. 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’,….)

    Like

  4. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  5. 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

    Like

  6. 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.

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s