How SQL Server Stores a GUID, and Why Random Ones Fragment
Few design choices start a fight faster than making a uniqueidentifier the primary key. One camp says GUIDs are fine, they are unique everywhere and the application can generate them. The other says GUIDs are a performance disaster that shred your indexes. Both are partly right, and the disagreement usually comes from arguing about “GUIDs” when the thing that actually matters is narrower: whether a random GUID is the clustering key, and how rows arrive. This post takes a GUID apart on the page, shows the peculiar way SQL Server sorts it, measures the fragmentation everyone warns about, and then looks at the sequential-GUID options that change the answer.
This continues the byte-level storage series alongside datetime, datetime2/date/time, and where each column lives in a record. If you have not watched a row get pulled apart with DBCC PAGE, this older post covers the mechanics. Every number below is from a SQL Server 2019 instance, and the scripts are included so you can reproduce them.
Sixteen bytes, stored mixed-endian
A uniqueidentifier is a 16-byte value.[1] That is the first thing worth sitting with: it is four times the width of an int and twice a bigint. Here is one on a data page, from DBCC PAGE dump style 3:
|
1 2 3 4 5 |
Slot 0 Column 1 Offset 0x4 Length 16 Length (physical) 16 id = f3e79fd4-907c-f111-8f84-b047e95a535b Memory Dump @0x...8060 0000000000000000: 10007800 d49fe7f3 7c9011f1 8f84b047 e95a535b ..x..........G.ZS[ |
Read the 16 bytes after the record header: d4 9f e7 f3 7c 90 11 f1 8f 84 b0 47 e9 5a 53 5b. Now compare that to the text form, f3e79fd4-907c-f111-8f84-b047e95a535b. The first three groups are byte-reversed on disk (little-endian): f3e79fd4 is stored d4 9f e7 f3, 907c is stored 7c 90, f111 is stored 11 f1. The last two groups, 8f84 and b047e95a535b, are stored in the order you read them. This mixed-endian layout is a Windows GUID convention, and it is the reason the sort order is going to look strange in a moment.
The width alone has a cost that has nothing to do with fragmentation. The clustering key is copied into every nonclustered index on the table, so a 16-byte clustered key makes every one of those indexes wider than it would be with a bigint, which means more pages, more memory to cache them, and more I/O to scan them. That tax is paid whether the GUID is random or sequential.
The sort order is not the byte order
Microsoft’s documentation says something easy to skim past: for uniqueidentifier, “ordering is not implemented by comparing the bit patterns of the two values.”[1] That is not a footnote; it is the whole story of why random GUIDs hurt. Watch what SQL Server does with three hand-picked values:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @demo TABLE ([g] uniqueidentifier); INSERT INTO @demo ([g]) VALUES ('01000000-0000-0000-0000-000000000000') /* first group set */ , ('00000000-0000-0000-0100-000000000000') /* a middle group */ , ('00000000-0000-0000-0000-000000000001'); /* last group set */ SELECT [guid] = [g] , [order_seq] = ROW_NUMBER() OVER (ORDER BY [g]) FROM @demo ORDER BY [g]; |
The result:
|
1 2 3 4 5 |
guid order_seq ------------------------------------ --------- 01000000-0000-0000-0000-000000000000 1 00000000-0000-0000-0100-000000000000 2 00000000-0000-0000-0000-000000000001 3 |
The value with 01 in the first group sorts smallest; the value with 01 in the last group sorts largest. SQL Server compares the groups roughly from the last one down to the first, so the trailing bytes are the most significant. Left-to-right byte order would have put 01000000-... last, not first.
Now connect it to NEWID. A NEWID value is random across all 16 bytes, so it is random in this ordering too. When the GUID is the clustered key, “random in the ordering” means every new row wants to land at a random point in the index, not at the end.
The fragmentation, measured
Here is the part people argue about, with numbers. Two tables, identical except for how the clustered-key GUID is generated: one defaults to NEWID() (random), the other to NEWSEQUENTIALID(). The important detail is how the rows arrive: one row per INSERT, the way an OLTP application actually writes. A single bulk INSERT ... SELECT would be sorted before it loads and would hide the effect entirely, which is a trap worth knowing about on its own.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
DROP TABLE IF EXISTS [dbo].[g_random]; DROP TABLE IF EXISTS [dbo].[g_seq]; CREATE TABLE [dbo].[g_random] ( [id] uniqueidentifier NOT NULL CONSTRAINT [pk_g_random] PRIMARY KEY CLUSTERED DEFAULT NEWID() , [filler] char(100) NOT NULL DEFAULT 'x' ); CREATE TABLE [dbo].[g_seq] ( [id] uniqueidentifier NOT NULL CONSTRAINT [pk_g_seq] PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID() , [filler] char(100) NOT NULL DEFAULT 'x' ); /* Trickle inserts: one row per statement, the realistic OLTP pattern. */ DECLARE @i int = 0; WHILE @i < 25000 BEGIN INSERT INTO [dbo].[g_random] DEFAULT VALUES; INSERT INTO [dbo].[g_seq] DEFAULT VALUES; SET @i += 1; END; SELECT [table] = OBJECT_NAME([ps].[object_id]) , [pages] = SUM([ps].[page_count]) , [avg_frag_pct] = CONVERT(decimal(5,1), AVG([ps].[avg_fragmentation_in_percent])) , [avg_page_full] = CONVERT(decimal(5,1), AVG([ps].[avg_page_space_used_in_percent])) FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'DETAILED') AS [ps] WHERE [ps].[object_id] IN (OBJECT_ID(N'dbo.g_random'), OBJECT_ID(N'dbo.g_seq')) AND [ps].[index_level] = 0 GROUP BY OBJECT_NAME([ps].[object_id]); |
After 25,000 single-row inserts into each:
|
1 2 3 4 |
table pages avg_frag_pct avg_page_full -------- ----- ------------ ------------- g_random 569 98.1 67.8 g_seq 391 0.5 98.7 |
Same rows, same width, same server. The random-GUID index is 98% fragmented, its pages are only 68% full, and it needed 569 pages. The sequential one is essentially unfragmented, packs its pages to 99%, and fits in 391 pages, about 45% fewer. The mechanism is the page split: a random key constantly needs to insert into the middle of a full page, so SQL Server splits the page in two, moves half the rows, and leaves both halves partly empty. Sequential keys always append to the end, so pages fill and stay put.
NEWSEQUENTIALID, and its fine print
NEWSEQUENTIALID generates a GUID greater than any it has produced on that computer since Windows started, which is exactly what keeps inserts appending instead of splitting.[2] It is not free of edges, though, and they are worth knowing before you reach for it:
- It is guessable. The docs are blunt: if privacy is a concern, do not use it, because the next value can be predicted and used to probe for rows. A
NEWIDvalue is not predictable; that unpredictability is a feature when the GUID is exposed to users. - It can only be a column DEFAULT. You cannot call it in a
SELECTor anINSERT ... VALUESlist; it exists only to feed a default constraint. - The sequence resets. After a Windows restart it can start from a lower range, and moving the database to another machine, an availability group failover, or Azure SQL Database can all create fresh clusters of sequential values. You get long runs that are locally increasing, not one global ever-rising line.
- It is still 16 bytes. Sequential fixes the fragmentation; it does not make the key any narrower, so the nonclustered-index and memory tax from the first section is unchanged.
“But my application generates sequential GUIDs”
This is where the topic gets genuinely subtle, and where a blanket “use sequential GUIDs” can quietly fail. Sequential means nothing in the abstract; it only helps if the values are increasing in SQL Server’s ordering, the group-by-group order from the last section. A GUID scheme that is sequential in normal left-to-right byte order is not sequential to SQL Server.
The current example is UUID version 7, the time-ordered UUID many application libraries now generate. A UUIDv7 puts a timestamp in its leading bytes, so it sorts nicely by time in standard byte order and in most other databases. But SQL Server treats those leading bytes as the least significant, so a stream of UUIDv7 values still lands at random positions in a uniqueidentifier clustered index and still fragments. Schemes built specifically for SQL Server’s ordering, often called sequential or COMB GUIDs, put the increasing part in the trailing bytes instead and do behave. The only way to be sure is to test: insert a sample and check whether they sort in the order you think they do, exactly as in the sort-order script above.
So, are GUIDs bad?
No. The problem is specific: a random GUID used as the clustering key, populated by trickle inserts. Change any one of those and the fragmentation argument mostly evaporates. A few ways out, roughly in order of how often they are the right call:
- Do not cluster on the GUID. Keep a narrow
intorbigintIDENTITYas the clustered key so inserts append, and put a nonclusteredUNIQUEconstraint on the GUID for lookups and cross-system identity. You still pay 16 bytes in that one index, but not in every index, and the table itself stays dense. - Cluster on a sequential GUID. If the GUID really must be the clustered key, generate it with
NEWSEQUENTIALID(or a verified SQL-ordered sequential GUID from the application), and accept the guessability trade-off. - Leave it random, but maintain it. For a small or rarely-inserted table, 98% fragmentation of a few hundred pages is not worth engineering around; a normal index-maintenance job handles it. Fragmentation is a symptom to weigh, not automatically a bug to fix, and my index reorg/rebuild script is one way to keep it in check.
GUIDs earn their keep when identity has to be generated away from the database, by clients, by distributed services, or by merge replication, which relies on uniqueidentifier to keep rows distinct across copies. In those cases the question is not “GUID or not” but “where does this GUID sit in the physical design,” and now you can answer it from the bytes up.
What tipped a GUID decision for you, the fragmentation, the width, or a distributed-identity requirement that made it worth the cost? I would like to hear it. Find me on Bluesky or LinkedIn.
References
- uniqueidentifier (Transact-SQL) – Microsoft Learn. The 16-byte size and the note that ordering is not by bit pattern. ↩
- NEWSEQUENTIALID (Transact-SQL) – Microsoft Learn. Increasing-since-boot behaviour, the privacy warning, the DEFAULT-only restriction, and the clusters that develop on failover or move. ↩