SQL Server の UUID v7 のデータ格納について

uniqueidentifier のデータ構造.sql

DECLARE @v uniqueidentifier = (SELECT NEWID())
SELECT @v, CAST (@v AS varbinary(16))

/*
   08E4BADA-DBD8-4CA8-A183-019F18F488F2
0x DABAE408 D8DB A84C A183 019F18F488F2

基本はリトルエンディアンだが、4 / 5 グループ目は反転していない
*/

-- https://techcommunity.microsoft.com/blog/azuredbsupport/lesson-learned-497understanding-the-ordering-of-uniqueidentifier-in-sql-server/4156066
-- https://learn.microsoft.com/ja-jp/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-ver16
-- https://datatracker.ietf.org/doc/rfc9562/
-- https://learn.microsoft.com/ja-jp/dotnet/framework/data/adonet/sql/comparing-guid-and-uniqueidentifier-values
-- https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450

-- ソートは 5 グループ目が第一の優先順位となる
-- https://learn.microsoft.com/ja-jp/archive/blogs/sqlprogrammability/how-are-guids-compared-in-sql-server-2005

UUID v7 の TimeStamp を 5 グループに移動するための関数.sql

CREATE OR ALTER FUNCTION dbo.convertuuid(@v uniqueidentifier)
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @r uniqueidentifier
SELECT 
	@r = 
	SUBSTRING(CAST(@v AS varbinary(16)),14,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),13,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),12,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),11,1) + 
	SUBSTRING(CAST(@v AS varbinary(16)),16,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),15,1) +
	
	SUBSTRING(CAST(@v AS varbinary(16)),7,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),8,1) +

	
	SUBSTRING(CAST(@v AS varbinary(16)),9,1) + 
	SUBSTRING(CAST(@v AS varbinary(16)),10,1)+ 

	SUBSTRING(CAST(@v AS varbinary(16)),4,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),3,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),2,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),1,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),6,1) +
	SUBSTRING(CAST(@v AS varbinary(16)),5,1) 
	RETURN @r
END

ソート順のテスト用.sql

DECLARE @t table (
	C1 int identity, 
	C2 uniqueidentifier DEFAULT NEWSEQUENTIALID()
	-- C2 uniqueidentifier DEFAULT NEWID()
	-- C2 varchar(36) DEFAULT NEWID()
)

DECLARE @cnt int = 1
WHILE(@cnt <= 10)
BEGIN
	INSERT INTO @t DEFAULT VALUES
	SET @cnt += 1
END

INSERT INTO @t VALUES('FFFFFFFF-FFFF-EF11-FFFF-006B8C909751')
SELECT * FROM @t ORDER BY C2 ASC

関数を使用したソートの制御

-- データ挿入時に TimeStamp が 5 グループ目になるように移動
DECLARE @t table (C1 int identity , C2 uniqueidentifier)
INSERT INTO @t VALUES
(dbo.convertuuid('01934255-ab81-7034-afa9-76822e888a6b')),
(dbo.convertuuid('01934255-ab86-7925-ba8e-71b49eb023fe')),
(dbo.convertuuid('01934255-ab96-7972-85b5-a97d1d9dd9df')),
(dbo.convertuuid('01934255-aba6-7336-9515-4003f41cde0d')),
(dbo.convertuuid('01934255-abb9-7add-8bd6-f5e9f6049030')),
(dbo.convertuuid('01934255-abc5-76e9-9a89-81580b5e4530')),
(dbo.convertuuid('01934255-abcd-7c82-bbae-163869ef62f2')),
(dbo.convertuuid('01934255-abdd-7344-be70-7da88c26921e')),
(dbo.convertuuid('01934255-abe8-7b94-82f8-7507a2040f0a')),
(dbo.convertuuid('01934255-abf7-72b2-9f30-4bbdaf61992d'))
SELECT * FROM @t ORDER BY C2 ASC
GO

-- UUID v7 で生成された値を uniqueidentifier にそのまま格納した場合の動作の確認
DECLARE @t table (C1 int identity , C2 uniqueidentifier)
INSERT INTO @t VALUES
('01934255-ab81-7034-afa9-76822e888a6b'),
('01934255-ab86-7925-ba8e-71b49eb023fe'),
('01934255-ab96-7972-85b5-a97d1d9dd9df'),
('01934255-aba6-7336-9515-4003f41cde0d'),
('01934255-abb9-7add-8bd6-f5e9f6049030'),
('01934255-abc5-76e9-9a89-81580b5e4530'),
('01934255-abcd-7c82-bbae-163869ef62f2'),
('01934255-abdd-7344-be70-7da88c26921e'),
('01934255-abe8-7b94-82f8-7507a2040f0a'),
('01934255-abf7-72b2-9f30-4bbdaf61992d')

SELECT * FROM @t ORDER BY C2 ASC
SELECT * FROM @t ORDER BY dbo.convertuuid(C2) ASC -- 関数で変換してソート

-- ユーザー定義関数なので並列実行のブロック要素となりえる

Public 化