NEWID vs NEWSEQUENTIALID: What Each Does to Your Index

NEWID vs NEWSEQUENTIALID decides how new uniqueidentifier keys land in your index. NEWID scatters them across every page. NEWSEQUENTIALID puts them in order at the end.

Why the Key Order Matters

A clustered index stores its rows in key order. When a new key is bigger than all the others, the row goes to the end of the last page. When a new key lands in the middle, SQL Server must fit it into a page that can be full. It then splits the page. About half of the rows move to a new page, and both pages are left half empty.

NEWID returns a random value, so every insert lands somewhere else. NEWSEQUENTIALID returns a value bigger than the previous one, so it behaves like an identity column. The demo below loads 20,000 rows with each default and compares the pages. It adds an int identity table as the baseline. The database is named GuidKeyDemo, so run the script on a test server.

IF DB_ID(N'GuidKeyDemo') IS NULL CREATE DATABASE GuidKeyDemo;
GO
USE GuidKeyDemo;
GO
DROP TABLE IF EXISTS dbo.OrdersRandom, dbo.OrdersSequential, dbo.OrdersInt;
CREATE TABLE dbo.OrdersRandom (
    OrderGuid uniqueidentifier NOT NULL CONSTRAINT DF_OrdersRandom DEFAULT NEWID(),
    InsertNo  int IDENTITY(1,1) NOT NULL,
    Note      char(100) NOT NULL,
    CONSTRAINT PK_OrdersRandom PRIMARY KEY CLUSTERED (OrderGuid)
);
CREATE TABLE dbo.OrdersSequential (
    OrderGuid uniqueidentifier NOT NULL CONSTRAINT DF_OrdersSequential DEFAULT NEWSEQUENTIALID(),
    InsertNo  int IDENTITY(1,1) NOT NULL,
    Note      char(100) NOT NULL,
    CONSTRAINT PK_OrdersSequential PRIMARY KEY CLUSTERED (OrderGuid)
);
CREATE TABLE dbo.OrdersInt (
    OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Note    char(100) NOT NULL
);

The loop inserts one row at a time, the way an application does. A single INSERT with many rows would hide the problem. SQL Server sorts those rows into key order before it writes them. A 50,000 row insert gives the same page count for both defaults.

SET NOCOUNT ON;
DECLARE @i int = 1;
BEGIN TRANSACTION;
WHILE @i <= 20000
BEGIN
    INSERT INTO dbo.OrdersRandom (Note) VALUES ('x');
    INSERT INTO dbo.OrdersSequential (Note) VALUES ('x');
    INSERT INTO dbo.OrdersInt (Note) VALUES ('x');
    SET @i += 1;
END;
COMMIT TRANSACTION;

Count the Pages

The next query reads the leaf level of each clustered index in DETAILED mode. It reports the number of pages, how full they are on average, and how fragmented they are. DETAILED reads every page, so keep it for test servers.

SELECT OBJECT_NAME(ips.object_id) AS TableName,
       ips.page_count AS Pages,
       CONVERT(decimal(5,1), ips.avg_page_space_used_in_percent) AS PageFullPercent,
       CONVERT(decimal(5,1), ips.avg_fragmentation_in_percent) AS FragmentationPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
WHERE ips.index_level = 0
  AND OBJECT_NAME(ips.object_id) LIKE N'Orders%'
ORDER BY TableName;

The NEWID table needs about half as many pages again as the sequential table. Its pages are about two thirds full, because every page split left empty space behind. The NEWSEQUENTIALID table is almost as compact as the int table. Pages are 98.7 percent full with the default fill factor, so a lower fill factor would only waste space. The NEWID numbers change from run to run. The gap between the tables doesn’t.

You can prove the order directly. The query below sorts each table by its GUID. It counts the rows that end up in a different position than the one they were inserted in.

SELECT N'NEWID' AS DefaultUsed, COUNT(*) AS RowsOutOfOrder
FROM (SELECT InsertNo, ROW_NUMBER() OVER (ORDER BY OrderGuid) AS GuidPosition
      FROM dbo.OrdersRandom) AS r
WHERE InsertNo <> GuidPosition
UNION ALL
SELECT N'NEWSEQUENTIALID', COUNT(*)
FROM (SELECT InsertNo, ROW_NUMBER() OVER (ORDER BY OrderGuid) AS GuidPosition
      FROM dbo.OrdersSequential) AS s
WHERE InsertNo <> GuidPosition;

Nearly every NEWID row sits in a different place than its insert order. Not one NEWSEQUENTIALID row does. That is the whole benefit: new keys go to the end.

What NEWSEQUENTIALID Cannot Do

NEWSEQUENTIALID works only as a column default. You can’t call it in a SELECT or assign it to a variable.

SELECT NEWSEQUENTIALID();

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.

Two more limits matter. After a Windows restart or a move to another server, new values can start from a lower range. The values stay unique, but for a while inserts land in the middle of the index again. And NEWSEQUENTIALID values are easier to predict than NEWID values. If a GUID acts as a secret, such as a download token, keep it random. Store it in its own column.

Switching the Default on a Table That Has Rows

Many teams meet this problem on an existing table. The old rows keep their random keys, and the new rows get sequential ones. The script below adds a second random table and switches the default on the first one. Then it adds 10,000 rows to each and prints the page count before and after.

DROP TABLE IF EXISTS dbo.OrdersRandomKeep;
CREATE TABLE dbo.OrdersRandomKeep (
    OrderGuid uniqueidentifier NOT NULL CONSTRAINT DF_OrdersRandomKeep DEFAULT NEWID(),
    InsertNo  int IDENTITY(1,1) NOT NULL,
    Note      char(100) NOT NULL,
    CONSTRAINT PK_OrdersRandomKeep PRIMARY KEY CLUSTERED (OrderGuid)
);
GO
SET NOCOUNT ON;
DECLARE @i int = 1;
BEGIN TRANSACTION;
WHILE @i <= 20000
BEGIN
    INSERT INTO dbo.OrdersRandomKeep (Note) VALUES ('x');
    SET @i += 1;
END;
COMMIT TRANSACTION;
GO
SELECT OBJECT_NAME(object_id) AS TableName, page_count AS PagesBefore
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE index_level = 0 AND OBJECT_NAME(object_id) IN (N'OrdersRandom', N'OrdersRandomKeep')
ORDER BY TableName;
GO
ALTER TABLE dbo.OrdersRandom DROP CONSTRAINT DF_OrdersRandom;
ALTER TABLE dbo.OrdersRandom ADD CONSTRAINT DF_OrdersRandom DEFAULT NEWSEQUENTIALID() FOR OrderGuid;
GO
SET NOCOUNT ON;
DECLARE @i int = 1;
BEGIN TRANSACTION;
WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.OrdersRandom (Note) VALUES ('x');
    INSERT INTO dbo.OrdersRandomKeep (Note) VALUES ('x');
    SET @i += 1;
END;
COMMIT TRANSACTION;
GO
SELECT OBJECT_NAME(object_id) AS TableName, page_count AS PagesAfter
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE index_level = 0 AND OBJECT_NAME(object_id) IN (N'OrdersRandom', N'OrdersRandomKeep')
ORDER BY TableName;

The NEWID numbers vary from run to run. The 10,000 new rows needed 162 pages on the switched table. They needed 232 on the table that stayed on NEWID. The old 20,000 rows didn’t move. They stay scattered until you rebuild the index. The switch helps every future insert and repairs nothing in the past.

Is a GUID Key Worth It?

You could argue that a GUID key is worth the cost. The application can create the key before it talks to the database. That is a real benefit, and it doesn’t require the clustered index. A common design keeps a narrow int or bigint as the clustered key. The GUID goes into a unique nonclustered index. A GUID takes 16 bytes and an int takes 4. Every nonclustered index carries the clustered key, so a narrow key keeps every index smaller.

What to Remember

If a uniqueidentifier must be the clustered key, use NEWSEQUENTIALID as the default, so new rows go to the end. The choice of NEWID vs NEWSEQUENTIALID is a choice between scattered and compact pages. The demo needed 468 pages for one and 323 for the other, with the same rows. Don’t lower the fill factor for a sequential key.

Keep NEWID for values that must be unpredictable, and don’t make them the clustered key. In the NEWID vs NEWSEQUENTIALID test, only one-row inserts showed the difference. Test with one-row inserts, because a multi-row insert hides the effect. When you finish, run the cleanup script.

USE master;
GO
ALTER DATABASE GuidKeyDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE GuidKeyDemo;

A random key is not a free choice, it is a bill that every insert pays.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.