SE の雑記

.NET9 で Guid.CreateVersion7 がサポートされたことで、.NET から UUID v7 のフォーマットの GUID を作成することができるようになりました。

SQL Server ベースのデータベースエンジンで UUID v7 フォーマットの GUID を格納するとどのような格納となるのかをまとめておきたいと思います。

SQL Server の UUID v7 サポートについては、すでにフィードバックが Support UUID v7 でフィードバックが上がっていますので、v7 サポートについては、こちらのフィードバックへの Vote も検討いただければと思います。

なお、本投稿では「SQL Server にデータを格納した際の挙動」をメインテーマにしており、格納するためのデータプロバイダーの挙動については触れておりませんので、その点は留意してください。

のいえさんの CysharpのOSS Top10まとめ / Ulid vs .NET 9 UUID v7 / MagicOnion もとても参考になりますのでこちらも確認していただければ。(この話題から本投稿をまとめようと思ったので)

SQL Server の GUID のフォーマット

SQL Server で GUID を生成した場合、次のようなフォーマットで作成がされます。

UUID のフォーマットについては、RFC 9562 Universally Unique IDentifiers (UUIDs) で確認をすることができます。

「3D78F2D3-2C4D-4E7F-8FF3-6891B01A976C」というようなフォーマットになっているため、UUID Version 4 で生成されていることになるかと思います。

Version Field 以外はランダムな値で構成されており、ランダム性の高い値となっています。

NEWSEQUENTIALID

SQL Server 特有の GUID フォーマットとしては NEWSEQUENTIALID があります。実装については UuidCreateSequential が使用されており、GUID フォーマットではあるのですが連続性を持った値が生成されるようになります。

SQL Server 単体で実装する場合、NEWSEQUENTIALID は列のデフォルト制約でのみ使用することができ、明示的に NEWSEQUENTIALID のフォーマットで GUID を生成する場合には、プログラム側で生成をする必要があります。

使用例としては次のようになります。

DROP TABLE IF EXISTS T1
GO
CREATE TABLE T1(
	C1 uniqueidentifier DEFAULT NEWSEQUENTIALID(),
	C2 int identity
)
GO

SET NOCOUNT ON
INSERT INTO T1 DEFAULT VALUES
GO 20
SELECT * FROM T1 ORDER BY C1
SELECT * FROM T1 ORDER BY C2

NEWSEQUENTIALID はソート可能な GUID フォーマットであるため、GUID / 連番のソート結果は同様となります。

1 グループ目の値が連番となるようなフォーマットで GUID が生成されており、Version Field については E となっているため、一般的ではないフォーマットで作成されていることになります。

「SQL Server でソート可能な GUID を生成したい」という場合には、NEWSEQUENTIALID の仕様が推奨されることが多いのですが、NEWSEQUENTIALID は DEFAULT制約でしか使用することがでません。

「プログラムが側から制御可能な汎用的なフォーマットによるソート可能な GUID (UUID)」として使用することはできないため、汎用性が低いものとなっているのではないでしょうか。

(UuidCreateSequential を使用すればソート可能な GUID を生成できるかもしれませんが、RFC 9562 に規定されている汎用的なフォーマットではないという認識です)

SQL Server で UUID v7 フォーマットを格納するとどうなるか

SQL Server で GUID を格納する際には uniqueidentifier を使用することになり、16 バイトのデータ型として格納を行います。

UUID v7 フォーマットのデータを uniqueidentifier に格納し、ソートを行うと次のようになります。(前節のクエリと同様に C1 / C2 それぞれでソートをしています)

UUID Version 7 のフォーマットは次のようになっています。

v4 の場合、最初のグループはランダムな値となっていましたが、v7 ではタイムスタンプが使用されており、最初のグループを使用してソートが行われれば、投入した GUID の順序でソートされた状態となります。

しかし前述のとおり、SQL Server では、v7 フォーマットでデータを格納してもデータの投入順序と格納順序はバラバラな状態となっており、データの断片化が激しく発生する状態となります。(期待しているのは C1 / C2 のソート順序が同様となることなのですが、C1 でソートすると期待したソート順になっていないことが確認できます)

これには、SQL Server の GUID のデータ格納方法と、ソート方法が影響しています。

SQL Server で GUID を格納した際のデータ格納内容

SQL Server で GUID を uniqueidentifier に格納すると実際のデータは以下のように格納されます。

「0193a3f1-796b-7684-bab7-7f7feacc4ce4」という UUID v7 のフォーマットのデータを格納した場合、実際のバイトデータとしては次のようになります。

SQL Server はデータを Little Endian で格納するケースが多いのですが、GUID も 3 グループ目 (Version Field) までは Little Endian で格納されているのですが、4 / 5 グループは Big Endian で格納されています。

これについては、How many ways are there to sort GUIDs? How much time do you have? が参考になりますが、現状 SQL Server ではこのようなデータ格納が行われており、この格納方式を変更することはできません。

SQL Server の GUID のソート (比較) 方法

SQL Server では、上述のようにデータが格納されていますが、これを使用してソートをどのように実施しているかについては、次のドキュメントが参考になります。

• How are GUIDs compared in SQL Server 2005?
• Lesson Learned #497:Understanding the Ordering of uniqueidentifier in SQL Server

The comparison is made by looking at byte "groups" right-to-left, and left-to-right within a byte "group". A byte group is what is delimited by the ‘-‘ character. More technically, we look at bytes {10 to 15} first, then {8-9}, then {6-7}, then {4-5}, and lastly {0 to 3}.

と記載されているように、「10 – 15」「8 – 9」「6 – 7」「4-5」「0-3」のグループの順でソートされるのが SQL Server のソートの仕様となってるようです。(GUID と uniqueidentifier 値の比較 で記載されているように、プログラム側でのソートとクエリによるソートはルールが違いますのでこの点も注意が必要です)

先ほどのデータであればソートの優先順位は次のようになります。

UUID v7 が期待通りソートされない理由

UUID v7 では、前方のグループにソートで利用できるタイムスタンプが格納されています。

UUID Version 7 のフォーマットは次のようになっています。

これは SQL Server のソート順序では最後のほうで比較される優先順位となります。

そのため、UUID v7 のデータをそのまま uniqueidentifier に格納をしても、ランダムな値が格納されている後半のブロックを使用したソートの優先度が高く、期待したソート順はになりません。

UUID v7 で格納された GUID をソート可能なようにバイトデータを入れ替える関数等 (以下のクエリであればユーザー定義関数として作成した convertuuid を使用してデータの入れ替えをしています) で、ソート順序意識して変換することで次のように期待したソート順とすることもできます。

しかし、「ソート時に UUID v7 をソート可能なフォーマットに変換する」方法は、「ユーザー定義関数を使用することのオーバーヘッド」や、「全件をソートしなくてはいけない」ため、「既に格納されている UUID v7 フォーマットのデータをソート可能なように変換する」ということは実際に使用するには難しいかと思います。

また、「データ格納時に UUID v7 フォーマットの GUID を SQL Server の uniqueidentifier でソート可能なように変換する」ということも実装としては可能かと思いますが、プログラム側で生成された GUID をそのままの形で使用することはできず「本来挿入したいデータとは異なる値になっている」「変換のオーバーヘッドが発生する」というような懸念があります。

参考: 変換関数のサンプル

上述のクエリで使用している「convertuuid」は以下のような実装となっています。先頭のブロックのタイムスタンプの値を GUID の後方に配置し、ソート時に優先順位の高いブロックとなるようにデータを移動させています。(適当に作ったものなので簡単な動作確認ぐらいしかできておらず、大量にデータを生成した際に期待した順序にならない可能性があるかもしれませんが)

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

varchar(36) で格納することによる問題

現状、uniqueidentifier は UUID v7 フォーマットによるソートをネイティブに対応していないため、UUID v7 フォーマットの GUID をそのまま格納しても SQL Server 側で期待したソート順にすることはできません。

現時点で UUID v7 フォーマットの GUID 値を格納し、ソート可能な状態にするためには文字列データ型で格納する必要があります。

データ型が文字列型であれば先頭からの比較となるため、期待したソートとなります。

ただし、uniqueidentifier であれば 16 バイトで格納できていたデータを倍以上のデータサイズ (36 バイト) で格納する必要があるため、データの格納効率は低い状態となるのは回避できないのではないでしょうか。

まとめ

「uniqueidentifier データ型を使用して UUID v7 フォーマットのデータを格納することはできるが、UUID v7 で期待するソート順序を意識した格納とはなっていない」というのが現時点の SQL Server ベースのデータベースエンジンの実装となるかと思います。

「UUID v7 フォーマットのデータを格納し、v7 のタイムスタンプを使用した期待されるソート順になるデータ型」は現状なく、UUID v7 の利点を活かせるネイティブなデータ型が存在していないので、v7 サポートの必要性については継続的なフィードバックをする必要があるのではないでしょうか。

また、SQL Server ベースのデータベースエンジンで UUID v7 を使用した場合、どのようにするのが適切なのかの指針が MS からアナウンスされると嬉しいですね。