Skip to content

Instantly share code, notes, and snippets.

@pwelter34

pwelter34/GenerateULID.sql

Last active March 17, 2026 01:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save pwelter34/ad2eb2a10cb65bbe268951e1e7d9a60e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save pwelter34/ad2eb2a10cb65bbe268951e1e7d9a60e to your computer and use it in GitHub Desktop.
Download ZIP
SQL Server ULID Implementation
Raw
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
Raw
NewULID.sql
CREATE FUNCTION [dbo].[NewULID]
(
@random BINARY(10), -- 80 bits of randomness, pass CRYPT_GEN_RANDOM(10)
@timestamp DATETIME2 = NULL -- NULL defaults to SYSUTCDATETIME()
)
RETURNS BINARY(16) -- 128-bit ULID: 48-bit timestamp + 80-bit random
AS
BEGIN
SET @timestamp = ISNULL(@timestamp, SYSUTCDATETIME());
-- Unix epoch milliseconds
DECLARE @ms BIGINT = DATEDIFF_BIG(MILLISECOND, '1970-01-01', @timestamp);
-- Extract 6 bytes big-endian (most significant byte first / network byte order)
DECLARE @ts BINARY(6) =
CAST(@ms / 0x10000000000 % 0x100 AS BINARY(1)) + -- byte 0 (MSB)
CAST(@ms / 0x100000000 % 0x100 AS BINARY(1)) + -- byte 1
CAST(@ms / 0x1000000 % 0x100 AS BINARY(1)) + -- byte 2
CAST(@ms / 0x10000 % 0x100 AS BINARY(1)) + -- byte 3
CAST(@ms / 0x100 % 0x100 AS BINARY(1)) + -- byte 4
CAST(@ms % 0x100 AS BINARY(1)); -- byte 5 (LSB)
-- Concatenate timestamp + randomness = 16 bytes
RETURN @ts + @random;
END
Raw
ParseULID.sql
CREATE FUNCTION [dbo].[ParseULID]
(
@ulid CHAR(26) -- 26-char Crockford Base32 string (e.g. from RC.StringifyULID)
)
RETURNS BINARY(16) -- 128-bit ULID: 48-bit timestamp + 80-bit random
AS
BEGIN
-- Crockford's Base32 alphabet (same as RC.StringifyULID)
DECLARE @alphabet CHAR(32) = '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
DECLARE @s CHAR(26) = UPPER(@ulid);
-- Decode each character to its 5-bit value (0–31) via position lookup.
-- CHARINDEX is 1-based; subtract 1 for the 0-based bit value.
DECLARE @v01 TINYINT = CHARINDEX(SUBSTRING(@s, 1, 1), @alphabet) - 1; -- char 1: 3 bits → b1[7:5]
DECLARE @v02 TINYINT = CHARINDEX(SUBSTRING(@s, 2, 1), @alphabet) - 1; -- char 2: 5 bits → b1[4:0]
DECLARE @v03 TINYINT = CHARINDEX(SUBSTRING(@s, 3, 1), @alphabet) - 1; -- char 3: 5 bits → b2[7:3]
DECLARE @v04 TINYINT = CHARINDEX(SUBSTRING(@s, 4, 1), @alphabet) - 1; -- char 4: 3+2 → b2[2:0] b3[7:6]
DECLARE @v05 TINYINT = CHARINDEX(SUBSTRING(@s, 5, 1), @alphabet) - 1; -- char 5: 5 bits → b3[5:1]
DECLARE @v06 TINYINT = CHARINDEX(SUBSTRING(@s, 6, 1), @alphabet) - 1; -- char 6: 1+4 → b3[0] b4[7:4]
DECLARE @v07 TINYINT = CHARINDEX(SUBSTRING(@s, 7, 1), @alphabet) - 1; -- char 7: 4+1 → b4[3:0] b5[7]
DECLARE @v08 TINYINT = CHARINDEX(SUBSTRING(@s, 8, 1), @alphabet) - 1; -- char 8: 5 bits → b5[6:2]
DECLARE @v09 TINYINT = CHARINDEX(SUBSTRING(@s, 9, 1), @alphabet) - 1; -- char 9: 2+3 → b5[1:0] b6[7:5]
DECLARE @v10 TINYINT = CHARINDEX(SUBSTRING(@s, 10, 1), @alphabet) - 1; -- char 10: 5 bits → b6[4:0]
DECLARE @v11 TINYINT = CHARINDEX(SUBSTRING(@s, 11, 1), @alphabet) - 1; -- char 11: 5 bits → b7[7:3]
DECLARE @v12 TINYINT = CHARINDEX(SUBSTRING(@s, 12, 1), @alphabet) - 1; -- char 12: 3+2 → b7[2:0] b8[7:6]
DECLARE @v13 TINYINT = CHARINDEX(SUBSTRING(@s, 13, 1), @alphabet) - 1; -- char 13: 5 bits → b8[5:1]
DECLARE @v14 TINYINT = CHARINDEX(SUBSTRING(@s, 14, 1), @alphabet) - 1; -- char 14: 1+4 → b8[0] b9[7:4]
DECLARE @v15 TINYINT = CHARINDEX(SUBSTRING(@s, 15, 1), @alphabet) - 1; -- char 15: 4+1 → b9[3:0] b10[7]
DECLARE @v16 TINYINT = CHARINDEX(SUBSTRING(@s, 16, 1), @alphabet) - 1; -- char 16: 5 bits → b10[6:2]
DECLARE @v17 TINYINT = CHARINDEX(SUBSTRING(@s, 17, 1), @alphabet) - 1; -- char 17: 2+3 → b10[1:0] b11[7:5]
DECLARE @v18 TINYINT = CHARINDEX(SUBSTRING(@s, 18, 1), @alphabet) - 1; -- char 18: 5 bits → b11[4:0]
DECLARE @v19 TINYINT = CHARINDEX(SUBSTRING(@s, 19, 1), @alphabet) - 1; -- char 19: 5 bits → b12[7:3]
DECLARE @v20 TINYINT = CHARINDEX(SUBSTRING(@s, 20, 1), @alphabet) - 1; -- char 20: 3+2 → b12[2:0] b13[7:6]
DECLARE @v21 TINYINT = CHARINDEX(SUBSTRING(@s, 21, 1), @alphabet) - 1; -- char 21: 5 bits → b13[5:1]
DECLARE @v22 TINYINT = CHARINDEX(SUBSTRING(@s, 22, 1), @alphabet) - 1; -- char 22: 1+4 → b13[0] b14[7:4]
DECLARE @v23 TINYINT = CHARINDEX(SUBSTRING(@s, 23, 1), @alphabet) - 1; -- char 23: 4+1 → b14[3:0] b15[7]
DECLARE @v24 TINYINT = CHARINDEX(SUBSTRING(@s, 24, 1), @alphabet) - 1; -- char 24: 5 bits → b15[6:2]
DECLARE @v25 TINYINT = CHARINDEX(SUBSTRING(@s, 25, 1), @alphabet) - 1; -- char 25: 2+3 → b15[1:0] b16[7:5]
DECLARE @v26 TINYINT = CHARINDEX(SUBSTRING(@s, 26, 1), @alphabet) - 1; -- char 26: 5 bits → b16[4:0]
-- Reconstruct 16 bytes from 26 × 5-bit values.
-- Each byte value is guaranteed 0–255 for valid Crockford Base32 input.
RETURN
-- Timestamp bytes (b1–b6, chars 1–10)
CAST( @v01 * 32 + @v02 AS BINARY(1)) + -- b1
CAST( @v03 * 8 + @v04 / 4 AS BINARY(1)) + -- b2
CAST( @v04 % 4 * 64 + @v05 * 2 + @v06 / 16 AS BINARY(1)) + -- b3
CAST( @v06 % 16 * 16 + @v07 / 2 AS BINARY(1)) + -- b4
CAST( @v07 % 2 * 128 + @v08 * 4 + @v09 / 8 AS BINARY(1)) + -- b5
CAST( @v09 % 8 * 32 + @v10 AS BINARY(1)) + -- b6
-- Random bytes (b7–b16, chars 11–26)
CAST( @v11 * 8 + @v12 / 4 AS BINARY(1)) + -- b7
CAST( @v12 % 4 * 64 + @v13 * 2 + @v14 / 16 AS BINARY(1)) + -- b8
CAST( @v14 % 16 * 16 + @v15 / 2 AS BINARY(1)) + -- b9
CAST( @v15 % 2 * 128 + @v16 * 4 + @v17 / 8 AS BINARY(1)) + -- b10
CAST( @v17 % 8 * 32 + @v18 AS BINARY(1)) + -- b11
CAST( @v19 * 8 + @v20 / 4 AS BINARY(1)) + -- b12
CAST( @v20 % 4 * 64 + @v21 * 2 + @v22 / 16 AS BINARY(1)) + -- b13
CAST( @v22 % 16 * 16 + @v23 / 2 AS BINARY(1)) + -- b14
CAST( @v23 % 2 * 128 + @v24 * 4 + @v25 / 8 AS BINARY(1)) + -- b15
CAST( @v25 % 8 * 32 + @v26 AS BINARY(1)); -- b16
END
Raw
Select.sql
SELECT dbo.[GenerateULID](CRYPT_GEN_RANDOM(10), DEFAULT) AS ULID
Raw
StringifyULID.sql
CREATE FUNCTION [dbo].[StringifyULID]
(
@ulid BINARY(16)
)
RETURNS CHAR(26) -- 26-char Crockford's Base32 string
AS
BEGIN
-- Crockford's Base32 alphabet (excludes I, L, O, U)
DECLARE @alphabet CHAR(32) = '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
-- Extract all 16 bytes
DECLARE @b01 TINYINT = CAST(SUBSTRING(@ulid, 1, 1) AS TINYINT); -- timestamp byte 0
DECLARE @b02 TINYINT = CAST(SUBSTRING(@ulid, 2, 1) AS TINYINT); -- timestamp byte 1
DECLARE @b03 TINYINT = CAST(SUBSTRING(@ulid, 3, 1) AS TINYINT); -- timestamp byte 2
DECLARE @b04 TINYINT = CAST(SUBSTRING(@ulid, 4, 1) AS TINYINT); -- timestamp byte 3
DECLARE @b05 TINYINT = CAST(SUBSTRING(@ulid, 5, 1) AS TINYINT); -- timestamp byte 4
DECLARE @b06 TINYINT = CAST(SUBSTRING(@ulid, 6, 1) AS TINYINT); -- timestamp byte 5
DECLARE @b07 TINYINT = CAST(SUBSTRING(@ulid, 7, 1) AS TINYINT); -- random byte 0
DECLARE @b08 TINYINT = CAST(SUBSTRING(@ulid, 8, 1) AS TINYINT); -- random byte 1
DECLARE @b09 TINYINT = CAST(SUBSTRING(@ulid, 9, 1) AS TINYINT); -- random byte 2
DECLARE @b10 TINYINT = CAST(SUBSTRING(@ulid, 10, 1) AS TINYINT); -- random byte 3
DECLARE @b11 TINYINT = CAST(SUBSTRING(@ulid, 11, 1) AS TINYINT); -- random byte 4
DECLARE @b12 TINYINT = CAST(SUBSTRING(@ulid, 12, 1) AS TINYINT); -- random byte 5
DECLARE @b13 TINYINT = CAST(SUBSTRING(@ulid, 13, 1) AS TINYINT); -- random byte 6
DECLARE @b14 TINYINT = CAST(SUBSTRING(@ulid, 14, 1) AS TINYINT); -- random byte 7
DECLARE @b15 TINYINT = CAST(SUBSTRING(@ulid, 15, 1) AS TINYINT); -- random byte 8
DECLARE @b16 TINYINT = CAST(SUBSTRING(@ulid, 16, 1) AS TINYINT); -- random byte 9
-- Encode 128 bits as 26 x 5-bit Crockford Base32 characters
-- 26 chars x 5 bits = 130 bits; top 2 bits of char 1 are always zero
RETURN
-- Timestamp (10 chars from 48 bits, 2 leading zero bits)
SUBSTRING(@alphabet, ((@b01 >> 5) & 0x07) + 1, 1) + -- char 1: bits 7-5 of b1 (3 bits, zero-padded to 5)
SUBSTRING(@alphabet, (@b01 & 0x1F) + 1, 1) + -- char 2: bits 4-0 of b1
SUBSTRING(@alphabet, ((@b02 >> 3) & 0x1F) + 1, 1) + -- char 3: bits 7-3 of b2
SUBSTRING(@alphabet, (((@b02 & 0x07) * 4) | ((@b03 >> 6) & 0x03)) + 1, 1) + -- char 4: bits 2-0 of b2 + bits 7-6 of b3
SUBSTRING(@alphabet, ((@b03 >> 1) & 0x1F) + 1, 1) + -- char 5: bits 5-1 of b3
SUBSTRING(@alphabet, (((@b03 & 0x01) * 16) | ((@b04 >> 4) & 0x0F)) + 1, 1) + -- char 6: bit 0 of b3 + bits 7-4 of b4
SUBSTRING(@alphabet, (((@b04 & 0x0F) * 2) | ((@b05 >> 7) & 0x01)) + 1, 1) + -- char 7: bits 3-0 of b4 + bit 7 of b5
SUBSTRING(@alphabet, ((@b05 >> 2) & 0x1F) + 1, 1) + -- char 8: bits 6-2 of b5
SUBSTRING(@alphabet, (((@b05 & 0x03) * 8) | ((@b06 >> 5) & 0x07)) + 1, 1) + -- char 9: bits 1-0 of b5 + bits 7-5 of b6
SUBSTRING(@alphabet, (@b06 & 0x1F) + 1, 1) + -- char 10: bits 4-0 of b6
-- Randomness (16 chars from 80 bits, same 5-bit pattern repeats)
SUBSTRING(@alphabet, ((@b07 >> 3) & 0x1F) + 1, 1) + -- char 11: bits 7-3 of b7
SUBSTRING(@alphabet, (((@b07 & 0x07) * 4) | ((@b08 >> 6) & 0x03)) + 1, 1) + -- char 12: bits 2-0 of b7 + bits 7-6 of b8
SUBSTRING(@alphabet, ((@b08 >> 1) & 0x1F) + 1, 1) + -- char 13: bits 5-1 of b8
SUBSTRING(@alphabet, (((@b08 & 0x01) * 16) | ((@b09 >> 4) & 0x0F)) + 1, 1) + -- char 14: bit 0 of b8 + bits 7-4 of b9
SUBSTRING(@alphabet, (((@b09 & 0x0F) * 2) | ((@b10 >> 7) & 0x01)) + 1, 1) + -- char 15: bits 3-0 of b9 + bit 7 of b10
SUBSTRING(@alphabet, ((@b10 >> 2) & 0x1F) + 1, 1) + -- char 16: bits 6-2 of b10
SUBSTRING(@alphabet, (((@b10 & 0x03) * 8) | ((@b11 >> 5) & 0x07)) + 1, 1) + -- char 17: bits 1-0 of b10 + bits 7-5 of b11
SUBSTRING(@alphabet, (@b11 & 0x1F) + 1, 1) + -- char 18: bits 4-0 of b11
SUBSTRING(@alphabet, ((@b12 >> 3) & 0x1F) + 1, 1) + -- char 19: bits 7-3 of b12
SUBSTRING(@alphabet, (((@b12 & 0x07) * 4) | ((@b13 >> 6) & 0x03)) + 1, 1) + -- char 20: bits 2-0 of b12 + bits 7-6 of b13
SUBSTRING(@alphabet, ((@b13 >> 1) & 0x1F) + 1, 1) + -- char 21: bits 5-1 of b13
SUBSTRING(@alphabet, (((@b13 & 0x01) * 16) | ((@b14 >> 4) & 0x0F)) + 1, 1) + -- char 22: bit 0 of b13 + bits 7-4 of b14
SUBSTRING(@alphabet, (((@b14 & 0x0F) * 2) | ((@b15 >> 7) & 0x01)) + 1, 1) + -- char 23: bits 3-0 of b14 + bit 7 of b15
SUBSTRING(@alphabet, ((@b15 >> 2) & 0x1F) + 1, 1) + -- char 24: bits 6-2 of b15
SUBSTRING(@alphabet, (((@b15 & 0x03) * 8) | ((@b16 >> 5) & 0x07)) + 1, 1) + -- char 25: bits 1-0 of b15 + bits 7-5 of b16
SUBSTRING(@alphabet, (@b16 & 0x1F) + 1, 1); -- char 26: bits 4-0 of b16
END
Raw
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),
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment