SQL Server ULID Implementation

GenerateULID.sql

CREATE FUNCTION [dbo].[GenerateULID]
(
    @random AS BINARY(10),
    @timestamp AS DATETIME2 = NULL
)
RETURNS VARCHAR(26)
AS
BEGIN
    -- Crockford's base32 alphabet
    DECLARE @alphabet VARCHAR(32) = '0123456789ABCDEFGHJKMNPQRSTVWXYZ'

    DECLARE @timeValue BIGINT
    DECLARE @timeEncoded VARCHAR(10) = ''

    -- Use current datetime if timestamp is not provided
    IF @timestamp IS NULL
        SET @timestamp = SYSUTCDATETIME()

    -- Get timestamp in milliseconds since Unix epoch
    SET @timeValue = DATEDIFF_BIG(MILLISECOND, '1970-01-01', @timestamp)

    -- Convert timestamp to base32 (10 characters)
    DECLARE @i INT = 1
    WHILE @i <= 10
    BEGIN
        SET @timeEncoded = SUBSTRING(@alphabet, (@timeValue % 32) + 1, 1) + @timeEncoded
        SET @timeValue = @timeValue / 32
        SET @i = @i + 1
    END

    -- Convert 10 bytes to a large integer representation, process the bytes in chunks to handle the large numbers
    DECLARE @byteValue INT
    DECLARE @chunk1 BIGINT = 0  -- First 5 bytes
    DECLARE @chunk2 BIGINT = 0  -- Last 5 bytes

    -- Process first 5 bytes (40 bits)
    SET @i = 1
    WHILE @i <= 5
    BEGIN
        SET @byteValue = CAST(SUBSTRING(@random, @i, 1) AS INT)
        SET @chunk1 = (@chunk1 * 256) + @byteValue
        SET @i = @i + 1
    END

    -- Process last 5 bytes (40 bits)
    SET @i = 6
    WHILE @i <= 10
    BEGIN
        SET @byteValue = CAST(SUBSTRING(@random, @i, 1) AS INT)
        SET @chunk2 = (@chunk2 * 256) + @byteValue
        SET @i = @i + 1
    END

    -- Convert first chunk to base32 (8 characters)
    DECLARE @randomEncoded1 VARCHAR(8) = ''
    SET @i = 1
    WHILE @i <= 8
    BEGIN
        SET @randomEncoded1 = SUBSTRING(@alphabet, (@chunk1 % 32) + 1, 1) + @randomEncoded1
        SET @chunk1 = @chunk1 / 32
        SET @i = @i + 1
    END

    -- Convert second chunk to base32 (8 characters)
    DECLARE @randomEncoded2 VARCHAR(8) = ''
    SET @i = 1
    WHILE @i <= 8
    BEGIN
        SET @randomEncoded2 = SUBSTRING(@alphabet, (@chunk2 % 32) + 1, 1) + @randomEncoded2
        SET @chunk2 = @chunk2 / 32
        SET @i = @i + 1
    END

    RETURN @timeEncoded + @randomEncoded1 + @randomEncoded2
END

Select.sql

SELECT dbo.[GenerateULID](CRYPT_GEN_RANDOM(10), DEFAULT) AS ULID

Transaction.sql

CREATE TABLE [dbo].[Transaction]
(
    [Id] INT IDENTITY (1000, 1) NOT NULL,

    -- the unique identifier (ULID) for the transaction
    [TransactionId] NVARCHAR(50) NOT NULL CONSTRAINT [DF_Transaction_TransactionId] DEFAULT (dbo.[GenerateULID](CRYPT_GEN_RANDOM(10), DEFAULT)),

    CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED ([Id] ASC),

    INDEX [UX_Transaction_TransactionId] UNIQUE NONCLUSTERED ([TransactionId] ASC),
);