UUID v4, UUID v7 and SQL Server GUIDs as primary keys

What version 7 actually changed

RFC 9562, published in 2024, replaced the old UUID specification and added three new versions. Version 7 is the one worth knowing about, because it is the first standard UUID designed to be used as a database key.

A version 4 UUID is 122 bits of randomness and nothing else. Two UUIDs generated a second apart have no relationship: their bits are independent, so consecutive inserts land in unrelated places in an index.

A version 7 UUID puts a 48-bit Unix timestamp in milliseconds at the front, in big-endian order, followed by the version and variant bits and roughly 74 bits of randomness. Two consequences follow directly from that layout:

• Sorting the raw bytes sorts by creation time, to the millisecond.
• Values created close together share a long prefix, so they land close together in an index.

Everything else about a UUID is unchanged: still 128 bits, still generated without coordination, still effectively free of collisions for any realistic volume. Version 7 keeps all of that and gives up one thing, which is discussed below.

Why random keys cost so much in an index

A B-tree index keeps its entries in sorted order across fixed-size pages. Insert a key that sorts after every existing key and it goes into the last page: one page touched, no reorganisation, and the pages you keep writing to stay in memory.

Insert a random key and it belongs somewhere in the middle. If the page it belongs in is full, the engine splits it: allocates a new page, moves about half the rows, and updates the parent. That costs writes, it costs log volume, and it leaves both pages half empty.

The effects compound in a predictable order: page splits, then fragmentation, then a lower fill factor, then more pages for the same rows, then a bigger memory footprint, then reads that miss cache. None of this shows up on a small table, which is exactly why the problem is usually discovered late.

SQL Server: the UNIQUEIDENTIFIER trap

SQL Server deserves its own section, because the obvious approach does not work and the reason is genuinely surprising.

SQL Server does not compare UNIQUEIDENTIFIER values by byte order. It compares the last six bytes first, then bytes 9 and 10, then 7 and 8, then 5 and 6, and only then the first four bytes. That ordering exists for historical reasons and it is not going to change.

The consequence: a version 7 UUID stored in a UNIQUEIDENTIFIER column does not sort in time order. The timestamp is at the front of the value, and SQL Server compares the front last. All the insert locality that version 7 was designed to give you is thrown away by the comparison rule.

You have three workable options:

• Store it as BINARY(16). Binary comparison is plain byte order, so version 7 sorts by time and behaves the way it was designed to. You lose the convenience of the GUID type and its display formatting, and you handle the conversion yourself.
• Use NEWSEQUENTIALID(). It generates values that increase in SQL Server's own comparison order, which is the whole point of it. Two caveats: it only works as a column default, so the application cannot generate the key before the insert, and the sequence can restart at a lower point after a server restart. It also derives part of its value from the machine, which Microsoft documents as a privacy consideration.
• Keep a random GUID, and do not cluster on it. Make the primary key non-clustered, and cluster on an identity column or a natural key instead. The GUID stays a unique, application-generated identifier; the physical ordering is decided by something sequential.

The third option is the one to reach for when you are retrofitting an existing schema, because it does not change the column type or any stored value.

PostgreSQL and MySQL

PostgreSQL stores table rows in a heap rather than clustering them by primary key, so a random UUID does not scatter the table itself, only the index. That makes the penalty real but noticeably smaller than in a clustered engine. The native uuid type stores 16 bytes and compares them in byte order, so version 7 sorts by time with no tricks. Recent PostgreSQL versions ship a built-in uuidv7() function; on older versions the generation happens in the application or in an extension.

MySQL with InnoDB is the case where this matters most. InnoDB clusters the table on the primary key, and every secondary index stores a copy of the primary key as its row pointer. A 16-byte key therefore inflates every secondary index on the table, and a random one scatters the table data as well as the indexes.

Two rules make UUID keys workable there. Store them as BINARY(16), never CHAR(36) — the text form more than doubles the size and drags that cost into every secondary index. And use a time-ordered value, so the clustered insert point stays at the end of the table.

What a v7 UUID gives away

Version 7 buys its performance by making the creation time public. Anyone holding one of your identifiers can read, to the millisecond, when the row was created. That is the one thing it gives up, and whether it matters depends entirely on the data.

For an order, an event or a log line, the creation time is usually visible anyway. For a user account it is a registration date. Holding two identifiers also reveals their order and the interval between them, which is enough to estimate how fast records are being created — the classic competitor-intelligence problem with sequential integer IDs, in a weaker form.

Note what is not leaked: version 7 still has around 74 random bits, so identifiers remain unguessable. Knowing one tells you nothing about the next. It is a confidentiality question about timing, not a predictability question.

If the creation time is sensitive, version 4 remains the right answer and you accept the index cost, or you keep a sequential internal key and expose a random one.

Choosing, in three questions

1. Is the identifier ever exposed to someone who should not know when the row was created? If yes, use version 4 and manage the index cost another way. If no, continue.
2. Is the database clustered on this key? SQL Server clustered primary keys and all InnoDB tables are. There, a time-ordered key is worth real effort — including storing it as binary.
3. Can the application generate the key before the insert? If it must — for client-side creation, offline work or merging across systems — then a generated version 7 UUID fits and NEWSEQUENTIALID() does not, because that only works as a server-side default.

For most new work the answer is version 7, stored in a type that compares by byte order. The UUID Generator produces both versions so you can compare them, and the GUID Generator emits the .NET and SQL Server literal formats when you need to paste one into code or a script.