As suggested by the front-end developer, I looked into using UUID as the primary key for a bunch of tables in our new system. From learning the pros and cons of random vs. sequential UUIDs, to the use of a non-clustered primary key in combination of a clustered index with a sort-able type, my research pointed me to UUIDv6, and an implementation of it.

It is able to generate UUIDs like the below (that is sequential):

UUIDv1                               UUIDv6 
------------------------------------ ------------------------------------ 
5714f720-1268-11e7-a24b-96d95aa38c32 1e712685-714f-6720-a23a-c90103f70be6 
68f820c0-1268-11e7-a24b-96d95aa38c32 1e712686-8f82-60c0-ac07-7d6641ed230d 
7ada38f0-1268-11e7-a24b-96d95aa38c32 1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 
8cc06fd0-1268-11e7-a24b-96d95aa38c32 1e712688-cc06-6fd0-a828-671acd892c6a 
9ea6a6b0-1268-11e7-a24b-96d95aa38c32 1e712689-ea6a-66b0-910c-dbcdb07df7a4

Which I thought SQL Server would gladly sort them for me in the clustered primary key (uniqueidentifier) column.

Little did I know how SQL Server would sort an uniqueidentifier column. Here's the ascending sort result:

UUIDv6 uniqueidentifier sorted 

Which is causing fragmentation as if using random UUIDs. This post explains how they were actually sorted.

The real question

Luckily, the system is still in development. Which of these options should I go for next?

  1. reorder the bytes so that the most/least significant bytes are where SQL Server expects them to be
UUIDv6                               UUIDv6 reordered bytes 
------------------------------------ ------------------------------------ 
1e712685-714f-6720-a23a-c90103f70be6 c90103f7-0be6-a23a-6720-1e712685714f 
1e712686-8f82-60c0-ac07-7d6641ed230d 7d6641ed-230d-ac07-60c0-1e7126868f82 
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c c1ebf8e6-fc8c-93f8-68f0-1e712687ada3 
1e712688-cc06-6fd0-a828-671acd892c6a 671acd89-2c6a-a828-6fd0-1e712688cc06 
1e712689-ea6a-66b0-910c-dbcdb07df7a4 dbcdb07d-f7a4-910c-66b0-1e712689ea6a
  1. convert the UUIDv6 to binary(16) and use that instead
UUIDv6                               UUIDv6 binary(16) 
------------------------------------ -------------------------------- 
1e712685-714f-6720-a23a-c90103f70be6 1e712685714f6720a23ac90103f70be6 
1e712686-8f82-60c0-ac07-7d6641ed230d 1e7126868f8260c0ac077d6641ed230d 
1e712687-ada3-68f0-93f8-c1ebf8e6fc8c 1e712687ada368f093f8c1ebf8e6fc8c 
1e712688-cc06-6fd0-a828-671acd892c6a 1e712688cc066fd0a828671acd892c6a 
1e712689-ea6a-66b0-910c-dbcdb07df7a4 1e712689ea6a66b0910cdbcdb07df7a4

Problem with option 1

The UUID standard embeds a 4-bit version field inside the ID. UUIDv6 (still non-standard) also follows that rule. The way I will reorder them is going to break this.

Problem with option 2

I'm not sure. Can hardly find anyone talking about it except this, which is going against the idea. Are there other pitfalls that I should be aware of in using the binary(16) type?



reorder the bytes so that the most/least significant bytes are where SQL Server expects them to be

Do that.

Option 1 is what SQL Server does natively with NEWSEQUENTIALID(), as wikipedia puts it

NEWSEQUENTIALID function returns 128-bit identifiers similar to UUIDs which are committed to ascend in sequence until the next system reboot

In SQL Server a UNIQUEIDENTIFIER is just a 128-bit binary type. It's not required to conform to the structure of a UUID.

And if it's sequentially-generated* you can make it the clustered index key. Having a narrower clustered index key is normally not worth the cost of having an extra and unnecessaary index.

*It's not a big deal if the location in the sort order of the sequential values changes occasionally or is generated in a few different places by a few different application servers.

  • Finally, I came across this page basically in agreement with your answer. That also did not conform to the standard but only focus on optimizing the UUID to be sorted properly in the DB. Although it's for MySQL, but the idea is the same. The benchmark there also gives me the confidence to choose option 1 over the others. Thanks! – oopoopoop Sep 10 '20 at 6:43

I would consider another option: cluster on a surrogate key (like Id int IDENTITY(1,1) NOT NULL), and make the app-generated UUID a nonclustered primary key.

This avoids the problems you called out with options 1 and 2, because you don't have to worry about the sorting / fragmentation issues in your base table, or potentially esoteric issues with clustering on binary.

It will also save you some space (int is smaller than uniqueidentifier and binary(16)), as the clustering key is included in every nonclustered index (as well as other tables that reference this table via foreign key).

  • I personally would like to avoid introducing a surrogate key. There will be no sorting nor fragmentation issues with options 1 and 2. Would you please elaborate more on the "potentially esoteric issues"? Also, from this post's accepted answer, quote "The alternative designs are simply not predictably and significantly better when you consider the extra data structures and complexity they introduce." – oopoopoop Sep 7 '20 at 10:08
  • 1
    @oopoopoop I totally understand you'd want to avoid that, but I thought I bring up the alternative option. As for esoteric issues, I just don't generally hear or read about folks clustering in binary columns. Any time I'm doing something uncommon, there's a chance I'll hit uncommon or undocumented problems. The storage space thing is the bigger concern in my mind. – Josh Darnell Sep 8 '20 at 13:23

I've worked with BINARY data types in SQL Server, but can't remember if I ever created a table with a clustered index on a BINARY column. With that said, the only thing I know to watch out for is this note in the docs:

Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server.

If you do go with Option 2 I recommend doing all conversions and manipulation of the BINARY value outside of SQL Server in application code.

  • Thanks for pointing that out. I saw that page as well and I plan to do all conversions outside of SQL Server. If I let it convert uniqueidentifier to binary(16) or vice versa, i will end up having the first 3 portions of the UUID byte-swapped. – oopoopoop Sep 9 '20 at 4:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.