3 ms·

I put in the work to store ULIDs in SQL Server's interesting UUIDv1-based sort order for uniqueidentifier columns. It seems to be providing the clustered index

I put in the work to store ULIDs in SQL Server's interesting UUIDv1-based sort order for uniqueidentifier columns. It seems to be providing the clustered index benefits I was hoping for so far (far less clustered index churn behavior than v4 Random UUIDs). An interesting digression to bring up with respect to this RFC because it is interesting to note that none of the proposed v6, v7, v8 UUIDs match the SQL Server sort order behavior and would still have a lot of clustered index churn unless new sort behaviors were allowed to be opted in. That might be something that this RFC would help to address by also making sort orders a bit more standard, though it does not directly make recommendations on that front. For what it is worth, it is the SQL Server sort order that is weird: it sorts the last group of the UUID string form first, which in v1 UUIDs corresponded to the MAC Address. Clustering by machine probably made sense by whoever decided on that sort order, at the time, but even with v1 UUIDs they probably would have got better results sorting by timestamp than by machine. (The ULID timestamp fits exactly into that "MAC address" group and it's just a matter of swapping the front 6 bytes to the back of the UUID when storing to uniqueidentifier.) Fun reference on UUID sort orders: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450 https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=10...

ComputerGuru 5y ago
It’s not just a question of whether you want to cluster by MAC address, it’s more of a fundamental disagreement as to the endianness of the UUID groups themselves, which is probably the biggest difference between a GUID and a UUID in the wild. Microsoft has used GUIDs to brand OS intervals for a long, long time - long enough that there’s a chance they came before UUID was a thing, but I haven’t dug into the history of it. Unfortunately converting between them without any db extensions is a PITA in the absence of native support. I’ve written about it at length here (SQLite in the example, but the fundamentals apply regardless): https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql/ https://neosmart.net/blog/2018/converting-a-binary-blob-guid...
- WorldMaker 5y ago
  That was the question specific to SQL Server storage sort order for clustered indexing and most relevant to ULID to uniqueidentifier "conversion"/storage, but yes there are endianness issues in the sort orders as well, which is why I made sure to include the full Raymond Chen blog post on many of the most common sort orders for GUIDs because it is quite the rabbit hole. (The "update" footnote link at the bottom of the article I posted to the Java order is especially wild if you fall into the rabbit hole.) (ETA: Also to answer your indirect question, Microsoft was definitely an early adopter of GUID/UUID. Though the inventor was Apollo Computer as a part of their complex network design: https://en.wikipedia.org/wiki/Apollo_Computer https://en.wikipedia.org/wiki/Apollo_Computer)
  - ComputerGuru 5y ago
    Thanks for clarifying - perhaps another reader will find that link for in-db conversion useful. And thanks for the interesting information and link to the Apollo Computer; I have some fodder for bedtime reading now!
    - WorldMaker 5y ago
      No problem. Yeah, for what it is worth (getting further aside), I gave up on supporting the ULID conversion directly in-db for that same reason: Base-32 math (which ULID uses for canonical string format) is tough enough in SQL before you add in the endian issues. The endian issues aren't as big of a deal in my storage sort order situation because the MAC address section that I use for the timestamp data most critical to the sort order doesn't suffer from the endian issue and the remainder of a ULID is entirely random and I'm not too concerned if the random section doesn't sort identically to the ULID sort order. (It would be great if it did, but it's not as critical as the more dominant timestamp order.) That said, .NET's GUID ToByteArray() handles the endian order for me and I'm somewhat confident it's correct so long as it is my .NET code doing ULID-to-uniqueidentifier conversions and not the scripts I tried to use directly in-db.
      - ComputerGuru 5y ago
        I’m using Postgres so I actually have it a little easier with its native sort order. I switched from NUlid that I was using from years back to this one [0], and the default .ToGuid() gets the desired sort order when used with Npgsql. Likewise though, no db-side generation of uuid - then again, isn’t that the benefit of using globally unique identifiers in the first place, to avoid needing to rely on the db to generate conflict-free identifiers, skipping HiLo or sequences, etc. Anyway, nice to meet another .NET Ulid user in the wild! 0: https://github.com/Cysharp/Ulid https://github.com/Cysharp/Ulid

ComputerGuru 5y ago

It’s not just a question of whether you want to cluster by MAC address, it’s more of a fundamental disagreement as to the endianness of the UUID groups themselves, which is probably the biggest difference between a GUID and a UUID in the wild. Microsoft has used GUIDs to brand OS intervals for a long, long time - long enough that there’s a chance they came before UUID was a thing, but I haven’t dug into the history of it. Unfortunately converting between them without any db extensions is a PITA in the absence of native support. I’ve written about it at length here (SQLite in the example, but the fundamentals apply regardless): https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql/ https://neosmart.net/blog/2018/converting-a-binary-blob-guid...

WorldMaker 5y ago
That was the question specific to SQL Server storage sort order for clustered indexing and most relevant to ULID to uniqueidentifier "conversion"/storage, but yes there are endianness issues in the sort orders as well, which is why I made sure to include the full Raymond Chen blog post on many of the most common sort orders for GUIDs because it is quite the rabbit hole. (The "update" footnote link at the bottom of the article I posted to the Java order is especially wild if you fall into the rabbit hole.) (ETA: Also to answer your indirect question, Microsoft was definitely an early adopter of GUID/UUID. Though the inventor was Apollo Computer as a part of their complex network design: https://en.wikipedia.org/wiki/Apollo_Computer https://en.wikipedia.org/wiki/Apollo_Computer)
- ComputerGuru 5y ago
  Thanks for clarifying - perhaps another reader will find that link for in-db conversion useful. And thanks for the interesting information and link to the Apollo Computer; I have some fodder for bedtime reading now!
  - WorldMaker 5y ago
    No problem. Yeah, for what it is worth (getting further aside), I gave up on supporting the ULID conversion directly in-db for that same reason: Base-32 math (which ULID uses for canonical string format) is tough enough in SQL before you add in the endian issues. The endian issues aren't as big of a deal in my storage sort order situation because the MAC address section that I use for the timestamp data most critical to the sort order doesn't suffer from the endian issue and the remainder of a ULID is entirely random and I'm not too concerned if the random section doesn't sort identically to the ULID sort order. (It would be great if it did, but it's not as critical as the more dominant timestamp order.) That said, .NET's GUID ToByteArray() handles the endian order for me and I'm somewhat confident it's correct so long as it is my .NET code doing ULID-to-uniqueidentifier conversions and not the scripts I tried to use directly in-db.
    - ComputerGuru 5y ago
      I’m using Postgres so I actually have it a little easier with its native sort order. I switched from NUlid that I was using from years back to this one [0], and the default .ToGuid() gets the desired sort order when used with Npgsql. Likewise though, no db-side generation of uuid - then again, isn’t that the benefit of using globally unique identifiers in the first place, to avoid needing to rely on the db to generate conflict-free identifiers, skipping HiLo or sequences, etc. Anyway, nice to meet another .NET Ulid user in the wild! 0: https://github.com/Cysharp/Ulid https://github.com/Cysharp/Ulid