A Separate Clustered Index for a Nonclustered GUID Primary Key

A separate clustered index lets a GUID stay your public primary key without deciding where the rows are stored. The primary key and the clustered index are two different jobs. SQL Server only bundles them by default.

One constraint, two jobs

A developer adds a table, writes PRIMARY KEY on a GUID column, and moves on. SQL Server quietly makes that primary key the clustered index. Now the random GUID decides where every new row lands. The team wanted a safe public identifier. They also signed up for random inserts without asking for them.

You can keep the GUID as the identifier and still pick a different clustered key. Add an ever-increasing identity column and cluster on that. The GUID remains the primary key, only nonclustered. Let me build both and show you what the catalog says.

Build the table with two jobs

The demo uses a database called SqlAuthorityDemo and drops it at the end.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;

Now the table. PublicId is the GUID, with a nonclustered primary key. RowId is an identity column with a unique clustered index. The three queries show the indexes, a GUID lookup and a range on RowId.

CREATE TABLE dbo.GuidRows (
    RowId    bigint IDENTITY NOT NULL,
    PublicId uniqueidentifier NOT NULL DEFAULT NEWID(),
    Payload  varchar(100) NOT NULL,
    CONSTRAINT PK_GuidRows PRIMARY KEY NONCLUSTERED (PublicId)
);
CREATE UNIQUE CLUSTERED INDEX CX_GuidRows ON dbo.GuidRows (RowId);

INSERT dbo.GuidRows (Payload) VALUES ('alpha'), ('beta'), ('gamma');

SELECT index_id, name, type_desc, is_primary_key, is_unique
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.GuidRows')
ORDER BY index_id;

DECLARE @id uniqueidentifier = (SELECT PublicId FROM dbo.GuidRows WHERE RowId = 1);
SELECT RowId, Payload FROM dbo.GuidRows WHERE PublicId = @id;

SELECT RowId, Payload FROM dbo.GuidRows WHERE RowId BETWEEN 1 AND 3 ORDER BY RowId;

Read the two index identities

Index 1 is CX_GuidRows. It is clustered, unique, and is_primary_key is 0. Index 2 is PK_GuidRows. It is nonclustered, unique, and is_primary_key is 1.

So the constraint and the clustering are separate properties. The GUID lookup returns RowId 1 with payload alpha. The range query returns RowIds 1 to 3 in order. Your generated GUID values will differ from mine, but the lookup finds the same row because I pick the GUID from RowId 1.

See how the GUID lookup finds the row

A lookup by GUID has one extra step now. Let me load 10,000 more rows into this table and into a second table that clusters on the GUID, the usual way. I will use that second table in a moment. Then I ask SQL Server for the plan of a GUID lookup, without running it.

CREATE TABLE dbo.GuidClustered (
    PublicId uniqueidentifier NOT NULL DEFAULT NEWID(),
    Payload  varchar(100) NOT NULL,
    CONSTRAINT PK_GuidClustered PRIMARY KEY CLUSTERED (PublicId)
);
GO
SET NOCOUNT ON;
DECLARE @i int = 0;

BEGIN TRANSACTION;
WHILE @i < 10000
BEGIN
    INSERT dbo.GuidRows (Payload) VALUES ('x');
    INSERT dbo.GuidClustered (Payload) VALUES ('x');
    SET @i += 1;
END;
COMMIT TRANSACTION;

SELECT OBJECT_NAME(ps.object_id) AS TableName, i.name AS IndexName,
       ps.page_count, CAST(ps.avg_fragmentation_in_percent AS decimal(5, 1)) AS FragmentationPct
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id IN (OBJECT_ID(N'dbo.GuidRows'), OBJECT_ID(N'dbo.GuidClustered'))
ORDER BY TableName, i.index_id;

The load step also returned a fragmentation report. I will read it after the plan.

SET SHOWPLAN_TEXT ON;
GO
SELECT RowId, Payload
FROM dbo.GuidRows
WHERE PublicId = '6F9619FF-8B86-D011-B42D-00C04FC964FF';
GO
SET SHOWPLAN_TEXT OFF;

The plan has two seeks. An Index Seek on PK_GuidRows finds the GUID. A Clustered Index Seek on CX_GuidRows, marked LOOKUP, then fetches the row by RowId. The nonclustered index carries the clustering key, so it always knows where to go.

What the comparison shows

Look at the table from the load step. I inserted the rows one at a time, the way an application does. GuidClustered is the default design, with the GUID as the clustered key. In my run its clustered index was above 95 percent fragmented. The identity clustered index in GuidRows was only a few percent.

Be honest about the trade, though. The GUID index in GuidRows is still fragmented, also above 95 percent in my run. The random inserts did not disappear. They moved to a narrow index that holds only the GUID and the RowId. That is much cheaper than shuffling whole rows. Your numbers will differ from run to run, but the shape should hold.

There is a cost too. The second index takes space and write work: PK_GuidRows used more pages than the table itself in my run. Measure your own workload before you copy this pattern.

Clean up

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Choose the clustered key for how the table is used, and the primary key for what identifies a row.

A primary key is not a clustering choice, it is a rule that can be nonclustered.

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