Add reference to Microsoft SQL implementation

[rmalayter]

Add a reference to a Microsoft SQL implementation

[codecov-io]

Codecov Report

Merging #38 into master will not change coverage. The diff coverage is n/a.

@@          Coverage Diff          @@
##           master    #38   +/-   ##
=====================================
  Coverage     100%   100%           
=====================================
  Files           2      2           
  Lines          93     93           
  Branches        5      5           
=====================================
  Hits           93     93

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update 4e0d919...5d280dd. Read the comment docs.

[alizain]

This one is interesting. Your note on SQL Server's sorting behaviour is certainly very strange. A couple questions:

1. Is dbo.ulid() simply different in its string representation, or is the binary layout different as well? (to accommodate SQL Server's behaviour)

2. As a corollary of the first question, is the binary representation of dbo.ulid and dbo.ulidStr exactly the same?

[rmalayter]

1) dbo.ulid() returns a UNIQUEIDENTIFIER data type, which is what most people will want on SQL Server, where UNIQUEIDENTIFIERs are commonly used as primary and foreign keys. 2) dbo.ulid() and dbo.ulidStr() have the same binary representation; in fact dbo.ulidStr() calls dbo.ulid() casts to VARBINARY, swaps the last 48 bits to the front (working around SQL's strange sort behavior) and then does the Crockford-base32 encoding. So the string returned is compatible with the original JS implementation in sort order and makeup.

More on the weird sorting of UNIQUEIDENTIFIER can be found here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/comparing-guid-and-uniqueidentifier-values, the kicker is

SqlGuid implements CompareTo using SQL Server behavior, in the last six bytes of a value are most significant

Actually ULIDs aren't a new idea in SQL Server land. Jimmy Nilsson came up with a ULID-like scheme called COMB way back in 2002 for SQL Server (really only differing in the orgin date and resultion of the timetamp. As you can see from that article, the timestamp is the last 48 bits of the GUID.)

If you think I should "normalize" the API to be more similar to the baseline implementations, I could make:

• dbo.ulidStr() returns a string representation, with the timestamp encoded first
• dbo.ulidUid() returns a UNIQUEIDENTIFIER with the timestamp at the end
• dbo.ulidBin() returns a BINARY(16) with the timestamp at the beginning
• dbo.ulid() is just a wrapper arround dbo.ulidStr()
• conversion functions from one form to another

[alizain]

I would prefer the normalization proposed. Since we're calling it ulid, the primary function (dbo.ulid() in this case) should produce a result that is compatible with those created in any other environment.

While I agree that ulidUid is useful in the context of SQLServer, a string with the timestamp at the end perhaps becomes a different thing altogether. I would be wary of including it as an API, but if you must, I would suggest big disclaimers in the documentation about differences. On that note, does SQLServer already offer a comparable primitive that puts the timestamp at the end?

Actually ULIDs aren't a new idea in SQL Server land.

Yup, just makes sense to have a UUID be a little more useful than simply randomness in certain situations.