GUID v4 vs GUID v7 as SQL Server PK

Question

This question was migrated from Stack Overflow because it can be answered on Database Administrators Stack Exchange. Migrated 4 days ago.

I've heard a lot that UUID v7 is performing much better in databases, when using it as primary key, due to the nature of how B-tree works. We have a huge problem with current implementation of PK as UUID v4, because it's led to a fragmentation of an index to 99%.

I was researching on how this fragmentation could be avoided in the future and the answer is UUID v7.

But recently I've found information that SQL Server is treating unique identifiers differently than other databases and that in the case of SQL Server this won't work as expected.

The question is: Would UUID v7 solve the problem with index fragmentation in SQL Server database or not?

• Paul White: Comments have been moved to chat; please do not continue the discussion here.

Answer 1

You are correct in saying that UUIDv7 would not be sorted correctly by SQL Server to get the benefit, if storing as uniqueidentifier. For example, the following 10 UUIDs

019b7535-2efa-7216-bc62-ec4618edc26d
019b7535-2efa-730e-b748-f1d3b12207e2
019b7535-2efa-7357-8f2a-ecb24ca50513
019b7535-2efa-75f6-992c-59446e240220
019b7535-2efa-7936-b6e5-d97e872ccd37
019b7535-2efa-7a73-95f7-3fa62af76206
019b7535-2efa-7b3e-96eb-38dc004f41ac
019b7535-2efa-7b53-b8a8-f9bdd7bb9bd1
019b7535-2efa-7c47-ac5d-bdff190afe4e
019b7535-2efa-7e46-bd94-d626a67b79f0

are sorted like this in SQL Server:

019b7535-2efa-7b3e-96eb-38dc004f41ac
019b7535-2efa-7a73-95f7-3fa62af76206
019b7535-2efa-75f6-992c-59446e240220
019b7535-2efa-7c47-ac5d-bdff190afe4e
019b7535-2efa-7e46-bd94-d626a67b79f0
019b7535-2efa-7936-b6e5-d97e872ccd37
019b7535-2efa-7216-bc62-ec4618edc26d
019b7535-2efa-7357-8f2a-ecb24ca50513
019b7535-2efa-730e-b748-f1d3b12207e2
019b7535-2efa-7b53-b8a8-f9bdd7bb9bd1

If you look at the spec RFC9562 it says:

UUIDv6 and UUIDv7 are designed so that implementations that require sorting (e.g. database indexes) SHOULD sort as opaque raw bytes, without need for parsing or introspection.

So you could store UUIDv7 as binary(16) which will sort correctly. To convert the text representation to binary(16) in T-SQL, do

convert(binary(16), replace(id, '-', ''), 2)

and just treat it as a binary blob, not as a uniqueidentifier.

• Антон Арбузов: Thanks! I think it's the way to go. But I have a follow up question: if database already have some values starting from 019b7535-2efa, is there a chance of collision? Or the random part of UUID v7 guarantees that collision is almost impossible as in UUID v4?
• Антон Арбузов: And another follow up, converting existing unique identifier column to binary(16) will it lock the table for the whole period of the converting?
• Charlieface: The spec guarantees it, although it may not be completely sequential if not all optional parts are used. Yes it will take a Sch-M lock preventing any other usage.
• Антон Арбузов: But, as far as I understood, although it's not completely sequential, it will significantly reduce fragmentation of an index?
• philipxy: @АнтонАрбузов Ask a new question in a new post, not in comments.

Answer 2

GUIDs as primary keys are a hilariously bad idea to begin with; to have them as clustered primary keys is a bad joke, and the joke is on you. For tables that have heavy incoming traffic, there is absolutely no way for proper GUIDs not to fragment to hell. Even for artificial GUIDs (where you control the first 64 bits), they will still do this.

If you HAVE to have a GUID as your primary key, make sure your primary key is not clustered.

Far more preferable is to have a simple bigint, probably with identity set, as your actual proper clustered primary key, and if you HAVE to have a GUID identifier for some reason, that is most likely an external key then. That should just be an additional field with a unique non-clustered index on it -- and a newguid() default constraint if you're feeling froggy) that you can very cheaply rebuild.

(The comments above ought to tell you that I am right here -- when people start talking about newsequentialquid, those are all mitigations for GUIDs to be sequential, and at that point, why the hell are you using GUIDs to begin with?)

• Charlieface: None of this addresses UUIDv7, which with the optional components is completely sequential. Why is UUIDv7 not a "proper GUID"? And newguid() is not a function in SQL Server, it's newid().
• Антон Арбузов: This is a legacy system, so I wasn't the one who make those decisions. But I'm the one who is dealing with them, so this question was aim to clarify sime things on how I can migrate existing architecture to a somewhat better solution.