SQL Server

Generate Monotonic ULIDs

See more ULID/UUID Examples

Generate a sequence of monotonic ULIDs all using the same seed time. This code sample generates a series of ULIDs with continuously increasing values.

Important: Chilkat's ULID functionality was introduced in v9.5.0.94.

SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @seedDt int
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @seedDt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- You can set the seed time to any desired value.
    -- In this example, the seed time is set to the current system date/time.
    DECLARE @success int
    EXEC sp_OAMethod @seedDt, 'SetFromCurrentSystemTime', @success OUT

    -- Generate the 1st ULID in the series.
    -- (i.e. the current UTC UNIX-time)
    DECLARE @utc int
    SELECT @utc = 1
    DECLARE @ulid nvarchar(4000)
    EXEC sp_OAMethod @seedDt, 'UlidGenerate', @ulid OUT, @utc

    PRINT @ulid

    -- Store the ULID in a Chilkat StringBuilder to generate the series.
    DECLARE @sbUlid int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUlid OUT

    EXEC sp_OAMethod @sbUlid, 'Append', @success OUT, @ulid

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < 50
      BEGIN
        EXEC sp_OAMethod @seedDt, 'UlidIncrement', @success OUT, @sbUlid
        EXEC sp_OAMethod @sbUlid, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    -- Sample output:

    -- 01GRGH30H8DMQ4F9A0XCTZHPGM
    -- 01GRGH30H8DMQ4F9A0XCTZHPGN
    -- 01GRGH30H8DMQ4F9A0XCTZHPGP
    -- 01GRGH30H8DMQ4F9A0XCTZHPGQ
    -- 01GRGH30H8DMQ4F9A0XCTZHPGR
    -- 01GRGH30H8DMQ4F9A0XCTZHPGS
    -- 01GRGH30H8DMQ4F9A0XCTZHPGT
    -- 01GRGH30H8DMQ4F9A0XCTZHPGV
    -- 01GRGH30H8DMQ4F9A0XCTZHPGW
    -- 01GRGH30H8DMQ4F9A0XCTZHPGX
    -- 01GRGH30H8DMQ4F9A0XCTZHPGY
    -- 01GRGH30H8DMQ4F9A0XCTZHPGZ
    -- 01GRGH30H8DMQ4F9A0XCTZHPH0
    -- 01GRGH30H8DMQ4F9A0XCTZHPH1
    -- 01GRGH30H8DMQ4F9A0XCTZHPH2
    -- 01GRGH30H8DMQ4F9A0XCTZHPH3
    -- 01GRGH30H8DMQ4F9A0XCTZHPH4
    -- 01GRGH30H8DMQ4F9A0XCTZHPH5
    -- 01GRGH30H8DMQ4F9A0XCTZHPH6
    -- 01GRGH30H8DMQ4F9A0XCTZHPH7
    -- 01GRGH30H8DMQ4F9A0XCTZHPH8
    -- 01GRGH30H8DMQ4F9A0XCTZHPH9
    -- 01GRGH30H8DMQ4F9A0XCTZHPHA
    -- 01GRGH30H8DMQ4F9A0XCTZHPHB
    -- 01GRGH30H8DMQ4F9A0XCTZHPHC
    -- 01GRGH30H8DMQ4F9A0XCTZHPHD
    -- 01GRGH30H8DMQ4F9A0XCTZHPHE
    -- 01GRGH30H8DMQ4F9A0XCTZHPHF
    -- 01GRGH30H8DMQ4F9A0XCTZHPHG
    -- 01GRGH30H8DMQ4F9A0XCTZHPHH
    -- 01GRGH30H8DMQ4F9A0XCTZHPHJ
    -- 01GRGH30H8DMQ4F9A0XCTZHPHK
    -- 01GRGH30H8DMQ4F9A0XCTZHPHM
    -- 01GRGH30H8DMQ4F9A0XCTZHPHN
    -- 01GRGH30H8DMQ4F9A0XCTZHPHP
    -- 01GRGH30H8DMQ4F9A0XCTZHPHQ
    -- 01GRGH30H8DMQ4F9A0XCTZHPHR
    -- 01GRGH30H8DMQ4F9A0XCTZHPHS
    -- 01GRGH30H8DMQ4F9A0XCTZHPHT
    -- 01GRGH30H8DMQ4F9A0XCTZHPHV
    -- 01GRGH30H8DMQ4F9A0XCTZHPHW
    -- 01GRGH30H8DMQ4F9A0XCTZHPHX
    -- 01GRGH30H8DMQ4F9A0XCTZHPHY
    -- 01GRGH30H8DMQ4F9A0XCTZHPHZ
    -- 01GRGH30H8DMQ4F9A0XCTZHPJ0
    -- 01GRGH30H8DMQ4F9A0XCTZHPJ1
    -- 01GRGH30H8DMQ4F9A0XCTZHPJ2
    -- 01GRGH30H8DMQ4F9A0XCTZHPJ3
    -- 01GRGH30H8DMQ4F9A0XCTZHPJ4
    -- 01GRGH30H8DMQ4F9A0XCTZHPJ5
    -- 01GRGH30H8DMQ4F9A0XCTZHPJ6

    EXEC @hr = sp_OADestroy @seedDt
    EXEC @hr = sp_OADestroy @sbUlid


END
GO