Difference between NEWID() and NEWSEQUENTIALID() in SQL Server

udayarumilli_uniqueDifference between NEWID() and NEWSEQUENTIALID() in SQL Server

Both NEWID() and NEWSEQUENTIALID() are used to generate GUID of data type UNIQUEIDENTIFIER in SQL Server.

The Basic difference between them is NEWID() generates GUID’s in random order whereas NEWSEQUENTIALID()  generates GUID’s in a sequential order.

We can generate a GUID just by calling NEWID() but we can’t directly use NEWSEQUENTIALID() for generating GUID’s from select statement.

Since NEWID() generates random GUID’s it leads to have more page splits in indexes compare to NEWSEQUENTIALID().

Examples:

  1. Now let’s see how both of them generates GUID’s    

To understand them properly lets create a table insert data and create cluster indexes.

Now we’ll see just by selecting data from them

You can see all “ProductID” column values are randomly generated.

Now we’ll look into the other table where we have used NEWSEQUENTIALID().

From the above picture we can see the “ProductID” values are sequentially generated.

  1. Now we‘ll see how these differentiate with the select statement.

NEWID() is generating a GUID. We’ll try with NEWSEQUENTIALID()

It results into an error message:

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.

  1. Difference between NEWID() and NEWSEQUENTIALID() in page allocation

 We have already inserted 20000 rows into two tables.

Product_NewID: Used NEWID()

Product_NewSEQID: Used NEWSEQUENTIALID()

 

 

For the same 20000 rows inserted in these tables we can check observe how pages are allocated for each of these tables.

For the table Product_NewID

Pages Scanned: 218

Avg. Bytes free per page: 2499.7

For the table Product_NewSEQID

Pages Scanned: 152

Avg. Bytes free per page: 69.2

From the above values we can say that there is higher fragmentation for the table which is using NEWID() when comparing with the table which is using NEWSEQUENTIALID().

Posted in SQL Development | Tagged , , , , , , , , , , , , , | Leave a comment

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz