dbForge Documenter for Oracle V6.0.85 lets users mark unique identifiers directly in the Data Editor, enhancing documentation precision.

A uniqueidentifier is 16 bytes with a peculiar sort order. Why random GUIDs fragment as a clustering key, and how NEWSEQUENTIALID and design fix it.

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: 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[ 12345 | Slot 0 Column 1 Offset 0x4 Length 16 Length (physical) 16id = f3e79fd4-907c-f111-8f84-b047e95a535b Memory Dump @0x...80600000000000000000: 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: Transact-SQL 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]; 1234567891011121314 | 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 @demoORDER BY [g]; ---|--- The result: guid order_seq ------------------------------------ --------- 01000000-0000-0000-0000-000000000000 1 00000000-0000-0000-0100-000000000000 2 00000000-0000-0000-0000-000000000001 3 12345 | guid order_seq------------------------------------ ---------01000000-0000-0000-0000-000000000000 100000000-0000-0000-0100-000000000000 200000000-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. Transact-SQL 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]); 12345678910111213141516171819202122232425262728293031323334353637 | 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 < 25000BEGIN 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] = 0GROUP BY OBJECT_NAME([ps].[object_id]); ---|--- After 25,000 single-row inserts into each: table pages avg_frag_pct avg_page_full -------- ----- ------------ ------------- g_random 569 98.1 67.8 g_seq 391 0.5 98.7 1234 | table pages avg_frag_pct avg_page_full-------- ----- ------------ -------------g_random 569 98.1 67.8g_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 `NEWID` value 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 `SELECT` or an `INSERT ... VALUES` list; 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 `int` or `bigint` `IDENTITY` as the clustered key so inserts append, and put a nonclustered `UNIQUE` constraint 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 1. uniqueidentifier (Transact-SQL) – Microsoft Learn. The 16-byte size and the note that ordering is not by bit pattern. ↩ 2. 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. ↩ ### Share this: * * Share on Bluesky (Opens in new window) Bluesky * Share on Mastodon (Opens in new window) Mastodon * Email a link to a friend (Opens in new window) Email * More * * Tweet * ### _Related_

I love GUIDs -- the uniqueidentifier data type in SQL Server. I use them for everything, domain names, unique error message, and for primary keys…