udayarumilli.com

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

/****** Drop the table if exists ******/
IF EXISTS (SELECT 1 FROM sysobjects WHERE TYPE = 'U' and NAME = 'Product_NewID')
BEGIN
    DROP TABLE Product_NewID;
END
GO
/****Create table with NEWID() ***/
CREATE TABLE Product_NewID(
    ProductID    UNIQUEIDENTIFIER DEFAULT(NEWID()),
    ProductName    VARCHAR(100) NOT NULL,
    ProductPrice    DECIMAL(10,3) NOT NULL DEFAULT(0.000),
    UpdatedDate    DATETIME NOT NULL DEFAULT(SYSDATETIME())
);
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE TYPE = 'U' and NAME = 'Product_NewSEQID')
BEGIN
    DROP TABLE Product_NewSEQID;
END
GO

/****Create table with NEWSEQUENTIALID() ***/
CREATE TABLE Product_NewSEQID(
    ProductID    UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID()),
    ProductName    VARCHAR(100) NOT NULL,
    ProductPrice    DECIMAL(10,3) NOT NULL DEFAULT(0.000),
    UpdatedDate    DATETIME NOT NULL DEFAULT(SYSDATETIME())
);
GO

/**** Create clustered indexes as below ****/
CREATE CLUSTERED INDEX ix_Product_NewID_ID ON Product_NewID(ProductID);
GO
CREATE CLUSTERED INDEX Product_NewSEQID_ID ON Product_NewSEQID(ProductID);
GO

/*** Now insert data into these two tables ****/
INSERT INTO Product_NewID (ProductName,ProductPrice)
VALUES ('Product_11109',233.56);
GO 20000

INSERT INTO Product_NewSEQID (ProductName,ProductPrice)
VALUES ('Product_11109',233.56);
GO 20000

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.

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

SELECT NEWID();
GO
SELECT NEWSEQUENTIALID();
GO

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.

3. 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()

DBCC SHOWCONTIG('Product_NewID');

DBCC SHOWCONTIG('Product_NewSEQID');

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().