When a GUID Clustered Primary Key Gets Expensive

A GUID primary key can solve a real application problem. Making a random GUID the clustered key also makes a storage decision, and that decision deserves its own review. Let’s look at when a GUID clustered primary key starts costing you more than it should.

Imagine an application that creates orders offline and later merges them from several regions. GUIDs let it generate identifiers before connecting to the database, with a negligible risk of collision. That is a good reason to use them.

Then someone writes this:

CREATE TABLE dbo.Orders (
    OrderID uniqueidentifier NOT NULL DEFAULT NEWID()
        PRIMARY KEY,
    CustomerID int NOT NULL
);

On this new table, SQL Server makes the primary key clustered by default. If a clustered index already exists, the default differs. Being explicit avoids the surprise.

Where Does the New Row Go?

The leaf pages of a clustered index contain the table’s rows. SQL Server maintains their logical key order. That does not mean the pages sit in consecutive positions on disk.

An increasing identity value usually sends a new row to the right-hand end of the index. A random value from NEWID() can belong between existing keys.

If the destination page has room, the insert fits. If it is full, SQL Server may split it, allocating another page and moving some rows. That creates additional work and transaction log records.

Two Costs Worth Checking

Page density. Splits can leave more free space across the index. Lower density means more pages for the same payload, potentially increasing reads and buffer pool demand. Later inserts can refill that space. The outcome depends on the workload and index maintenance.

Key width. A GUID takes 16 bytes, compared with 4 for an int and 8 for a bigint. Nonclustered indexes use the clustered key to locate the underlying row. A wider locator can therefore increase their size too, with details depending on each index definition.

Those costs can affect capacity planning. They do not establish a fixed multiplier for your invoice, backup size, restore time, or disaster recovery traffic. Measure those separately.

What the Small Test Shows

The saved test script records 50,000 single-row inserts into three tables on SQL Server 2025. Their business columns match, but their key types differ: int, bigint, and GUID.

In the recorded run, the GUID clustered index used 1,292 leaf pages at 68.8 percent fullness. The bigint version used 848 at 99.0 percent fullness. That is about 52 percent more leaf pages for the GUID version in this test.

A nonclustered index on CustomerID used 87 pages with the int clustered key, 112 with bigint, and 161 with GUID. The corresponding index records were 12, 16, and 24 bytes, including record overhead.

Insert times were 9.8, 10.3, and 11.2 seconds respectively. This small, single-session test does not establish a general throughput penalty or isolate every source of overhead.

This is a key-type comparison, not a benchmark of the two-key design below. The bigint test table does not retain a GUID column or its unique index. You cannot use its page count as the expected size after adopting that design.

Keep the GUID, Choose the Clustered Key Separately

When the application needs GUIDs, one option is a separate increasing clustered key:

CREATE TABLE dbo.Orders (
    OrderRowID bigint IDENTITY(1,1) NOT NULL,
    OrderID uniqueidentifier NOT NULL DEFAULT NEWID(),
    CustomerID int NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED (OrderID)
);

CREATE UNIQUE CLUSTERED INDEX CX_Orders
    ON dbo.Orders (OrderRowID);

The application can still supply its GUID. The unique bigint index now controls the clustered ordering and provides the locator used by other nonclustered indexes.

This can reduce disruptive random inserts into the table’s clustered pages. It does not eliminate page splits: the GUID primary key remains a random nonclustered index, and updates can also require more space.

There is a tradeoff. You add a bigint column and maintain a separate GUID index. GUID lookups may need an extra lookup into the clustered index. Highly concurrent inserts can also contend for the last page of an increasing index.

Test the complete design with your actual indexes, reads, writes, and concurrency. Compare total allocated space across all indexes, logical reads, and insert latency. Changing an existing clustered index also needs a planned migration, not just this new-table example.

What About NEWSEQUENTIALID()?

NEWSEQUENTIALID() can reduce random insertion and page splits while keeping a GUID clustered key. It still uses 16 bytes.

It works only as a column default, so it does not generate an ID in your offline application. Values can be predictable, and after a restart they can begin in a lower range. Moving or failing over to another computer also affects the sequence. Microsoft documents these limits in its NEWSEQUENTIALID reference.

The Rule Worth Remembering

A primary key enforces row identity. A clustered index organizes the table’s leaf-level storage. You can choose them separately.

Narrow, unique, stable, and increasing are useful clustered-key properties. A random GUID is wide and not increasing, so it misses two of those goals. The SQL Server index design guide explains why those properties matter.

Keep the identifier your application needs. Choose the storage design your workload can justify.

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