SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2025 の JSON インデックスのデータ格納方法について

leave a comment

Announcing the Public Preview of JSON index in SQL Server 2025 でアナウンスがありましたが、SQL Server 2025 では、JSON インデックスがサポートされるようになります。

SQL Server 2022 / 投稿時点の Azure SQL Database では、JSON インデックスはサポートされていなかったため、JSON データへのインデックスの追加 に記載されているように、計算列を使用して JSON フォーマットのデータのプロパティを実体を持つ列として切り出して、インデックスを作成するという方法を使用していました。

SQL Server 2025 ではネイティブで JSON インデックスをサポートし、JSON データ型の列に対して直接インデックスを作成することができるようになりました。

SQL Server 2025 CTP 2.0 の JSON インデックスがどのようにデータを格納しているかを把握しておきたかったので調べてみました。

Azure SQL Database の JSON サポートとの違い

Azure SQL Database では先行して JSON サポートの一般提供が開始されていますが、SQL Database とはいくつかの違いがあり、現時点では SQL Server 2025 でしか実装されていない内容があります。

これらの内容は現時点の SQL Database ではサポートされていないようです。

Array wildcard and range support については、SQL Database でもサポートされているようでした。

 

JSON インデックスのデータ格納方法

通常のインデックスであれば、テーブルに対してインデックスが設定されます。

JSON インデックスの場合も CREATE JSON INDEX でテーブルに対してインデックスを作成しますが、テーブルに対してのインデックスではなく、「json_index_<テーブルのオブジェクト ID>_<識別子>」というような、内部テーブル (INTERNAL_TABLE) として、JSON インデックスのデータが格納されます。

このテーブルは内部テーブルのため、通常のユーザーセッションでは確認はできませんが、専用管理者接続 (DAC : Dedicated Administrator Connection) であればテーブルの内容を確認することができます。

実際には次のようなデータが格納された状態となります。

image

JSON インデックスごとに一つの内部テーブル (+ 内部テーブルに対しての非クラスター化インデックス) が作成され、JSON データがプロパティで分割されてデータが格納されていることが確認できます。

JSON インデックスがどのようにデータを格納しているかについては、この内部テーブルのデータを確認することで把握することができるようです。

 

JSON インデックスが使用された場合、次のように JSON インデックスを使用された操作として実行プランが表示されます。

image

JSON インデックスの制約

JSON インデックスの基本制約

JSON インデックスの制約については Restrictions に記載されています。

次の内容は特徴的制約となるのではないでしょうか。

  • JSON インデックスを作成するテーブルには、プライマリキーが必要
  • JSON インデックスは 1 列に対して 1 つしか設定することができない

 

制約に「Data compression option isn’t supported.」が記載されていましたが、ページ圧縮はサポートされているようでした。以下のようなクエリを実行したところエラーになるかと思っていたのですが、エラーにならず実行することができました。

CREATE JSON INDEX JIX_json_t01_04 ON json_t01(json_column_04) WITH(DATA_COMPRESSION=PAGE) 

 

ただし、現時点では圧縮されるのは、内部テーブルのクラスター化インデックスの領域となるようで、非クラスター化インデックスの領域は圧縮されていないようでした。

DECLARE @tableName varchar(100) = 'json_t01'
SELECT ao.name, ao.object_id, ao.type_desc, ao.is_ms_shipped,p.index_id, i.type_desc, p.rows, p.data_compression_desc,ps.reserved_page_count, ps.used_page_count
FROM sys.all_objects AS ao
INNER JOIN sys.partitions AS p
	ON p.object_id = ao.object_id
INNER JOIN sys.dm_db_partition_stats AS ps
	ON ps.object_id = ao.object_id AND ps.index_id = p.index_id
INNER JOIN sys.indexes AS i
	ON ps.object_id = i.object_id  AND ps.index_id = i.index_id
WHERE ao.name like '%' + (SELECT CAST(object_id AS varchar(100)) FROM sys.objects WHERE name = @tableName) + '%'
ORDER BY name, index_id

 

1/2 行目が非圧縮、7,8 行目が圧縮した同内容に対しての JSON インデックスとなるのですが、ページ圧縮の効果は出ていそうな雰囲気がありました。

image

ワイルドカードのサポート状況

JSON インデックスは、デフォルトではすべてのプロパティをインデックスの対象としますが、 JSON PATH を使用して、特定のプロパティのみインデックスを作成することができます。検索で使用するプロパティ単位でインデックスを作成することはできず、インデックスに含めるプロパティは一つの JSON インデックスに含める必要があります。

現状、私が検証していて気になったのは「配列に対してのワイルドカードをサポートしていない」ように見えることしょうか。

SQL Server 2025 の JSON では Array wildcard and range support に記載されているように、配列に対してのワイルドカード指定が一部の操作でサポートされるようになりました。

しかし、JSON インデックスの JSON PATH ではワイルドカード指定はサポートされていないようで [*] でインデックスを作成しようとするとエラーとなってしまいました。

インデックス作成時に JSON PATH を指定せずにインデックスを作成すれば、配列内のすべての要素に対してインデックスが作成されるのですが、また、私が検証していた範囲では、JSON インデックスの使用を期待して、JSON_CONTAINS でワイルドカードを指定した検索を実行してもインデックスを使用させることができておらず、配列に対しての効率の良い動的な検索は難しそうな雰囲気がありました。

(配列内のインデックス番号を明示的に指定した場合は期待した動作になっているのですが)

 

まとめ

現状の動作では、JSON インデックスを使用する場合、既存の JSON に対しての検索効率の向上は難しく、「JSON インデックスが使用されるような JSON の構造」にする必要がありそうな雰囲気がありましたが、JSON のデータを使用する場合、JSON インデックスを活用できるかは意識しておきたいですね。

Share

Written by Masayuki.Ozawa

5月 25th, 2025 at 10:15 pm

Leave a Reply