SQLServer(T-SQL)でulid

はじめに

ulid (Universally Unique Lexicographically Sortable Identifier) というものがあるらしく、T-SQLで生成するストアドプロシージャを書いてみました。
https://github.com/ulid/spec
https://qiita.com/kai_kou/items/b4ac2d316920e08ac75a

ulid とは

Timestamp：48bit整数、UNIX時間(ミリ秒)
Randomness：80bit乱数
をBase32 Encodingしたものらしい。

検証環境

Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)
Jul 5 2017 23:41:17
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 17134: ) (Hypervisor)

T-SQL

USE TestDB;
GO

IF OBJECT_ID('dbo.get_ulid', 'P') IS NOT NULL DROP PROCEDURE dbo.get_ulid;  
GO  

CREATE PROCEDURE dbo.get_ulid
@result VARCHAR(26) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @characters VARCHAR(32) = '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
  DECLARE @timestamp BIGINT = DATEDIFF_BIG(ms,'1970/1/1',SYSUTCDATETIME());
  DECLARE @randomness INT = FLOOR((RAND(CONVERT(INT, CONVERT(VARBINARY, NEWID()))) * 32));
  DECLARE @remainder INT
  DECLARE @quotient BIGINT = @timestamp
  DECLARE @count INT = 0;
  --DECLARE @result VARCHAR(26) = '';
  SET @result = ''

  WHILE @quotient > 0
  BEGIN
    SET @remainder = @quotient %32
    SET @quotient = @quotient / 32
    SET @result = SUBSTRING(@characters, @remainder + 1, 1) + @result
  END
  SET @result = RIGHT('0000000000' + @result, 10)

  WHILE @count < 16
  BEGIN
    SET @randomness = FLOOR((RAND(CONVERT(INT, CONVERT(VARBINARY, NEWID()))) * 32));
    SET @result = @result + SUBSTRING(@characters, @randomness + 1, 1);
    SET @count = @count + 1;
  END
  --PRINT @result
  --SELECT @result AS 'ulid'
  RETURN 0
END
GO

実行結果

DECLARE @ulid VARCHAR(26);
EXECUTE dbo.get_ulid @ulid OUTPUT
PRINT @ulid

01D12R8RKKRCTGY9DCDX5ZXBT6

おわりに

きちんと実装を読んで作ったわけでは無いので、正確さは保証できかねます。。。