スパース列の使用

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Database Azure SQL Managed Instance

スパース列は、NULL 値用にストレージが最適化されている通常の列です。 スパース列によって、NULL 以外の値を取得するためのオーバーヘッドは増大しますが、NULL 値に必要となる領域は削減されます。 少なくとも 20 ～ 40% の領域を削減できる場合は、スパース列の使用を検討してください。 スパース列および列セットを定義するには、 CREATE TABLE または ALTER TABLE ステートメントを使用します。

スパース列は、列セットおよびフィルター選択されたインデックスと併用できます。

• 列セット

  INSERT、UPDATE、DELETE の各ステートメントは、スパース列を名前で参照できます。 ただし、テーブルのすべてのスパース列を 1 つの XML 列に結合して表示および操作することもできます。 この列を列セットと呼びます。 列セットの詳細については、「 列セットの使用」を参照してください。

• フィルター選択されたインデックス

  スパース列は、NULL 値の行が多数あるため、フィルター選択されたインデックスに特に適しています。 スパース列でフィルター選択されたインデックスを使用すると、値が設定された行にのみインデックスを作成できます。 これにより、より小さく効率的なインデックスが作成されます。 詳細については、「 Create Filtered Indexes」を参照してください。

スパース列とフィルター選択されたインデックスを併用することで、Windows SharePoint Services などのアプリケーションでは、SQL Server を使って多数のユーザー定義プロパティを効率よく格納およびアクセスできます。

スパース列のプロパティ

スパース列には次の特性があります。

• SQL Server データベース エンジンは、列定義で SPARSE キーワードを使って、その列での値の格納を最適化します。 このため、列値が NULL である行がテーブルに含まれている場合、その値はストレージを必要としません。

• スパース列を含んでいるテーブルのカタログ ビューは、一般的なテーブルのものと同じです。 sys.columns カタログ ビューは、テーブル内の各列の行で構成され、列セットが定義されていればそれを含んでいます。

• スパース列は、論理テーブルではなく、ストレージ層のプロパティです。 したがって、SELECT ... INTO ステートメントによって、スパース列のプロパティが新しいテーブルにコピーされることはありません。

• COLUMNS_UPDATED 関数は、DML アクションで更新されたすべての列を示す varbinary 値を返します。 COLUMNS_UPDATED 関数から返されるビットは次のとおりです。

  • スパース列が明示的に更新された場合は、そのスパース列の対応するビットが 1 に設定され、列セットのビットが 1 に設定されます。

  • 列セットが明示的に更新された場合は、列セットのビットが 1 に設定され、そのテーブル内のすべてのスパース列のビットが 1 に設定されます。

  • 挿入操作では、すべてのビットが 1 に設定されます。

  列セットの詳細については、「 列セットの使用」を参照してください。

次のデータ型は SPARSE と指定できません。

geography
geometry
image
ntext

text
timestamp
ユーザー定義データ型

データ型ごとの領域の推定節約量

スパース列は、同一データが SPARSE とマークされていない場合に比べて、NULL 以外の値により多くのストレージ領域を必要とします。 次の表は、各データ型の使用領域を示したものです。 NULL の比率 列は、正味 40% の領域を節約するために必要な NULL データの割合を示します。

固定長データ型

データの種類	非スパース バイト数	スパース バイト数	NULL の比率
bit	0.125	5	98%
tinyint	1	5	86%
smallint	2	6	76%
int	4	8	64%
bigint	8	12	52%
real	4	8	64%
float	8	12	52%
smallmoney	4	8	64%
money	8	12	52%
smalldatetime	4	8	64%
datetime	8	12	52%
uniqueidentifier	16	20	43%
date	3	7	69%

Precision-Dependent-Length データ型

データの種類	非スパース バイト数	スパース バイト数	NULL の比率
datetime2(0)	6	10	57%
datetime2(7)	8	12	52%
time(0)	3	7	69%
time(7)	5	9	60%
datetimetoffset(0)	8	12	52%
datetimetoffset (7)	10	14	49%
decimal/numeric(1,s)	5	9	60%
decimal/numeric(38,s)	17	21	42%
vardecimal(p,s)	控えめな推定値として decimal 型を使用してください。

Data-Dependent-Length データ型

データの種類	非スパース バイト数	スパース バイト数	NULL の比率
sql_variant	基になるデータ型で異なります。
varchar または char	2*	4*	60%
nvarchar または nchar	2*	4*+	60%
varbinary または binary	2*	4*	60%
xml	2*	4*	60%
hierarchyid	2*	4*	60%

* 長さは、型に含まれているデータの平均に 2 バイトまたは 4 バイトを加えた長さに等しくなります。

スパース列の更新に必要なインメモリ オーバーヘッド

スパース列を含むテーブルをデザインする場合は、行を更新するときにテーブル内の NULL 以外のスパース列ごとに追加の 2 バイトが必要になることに注意してください。 この追加のメモリ要件により、(このメモリ オーバーヘッドを含む) 合計行サイズが 8019 を超え、列を行外に出すことができないと、更新がエラー 576 で予期せずに失敗する可能性があります。

たとえば、bigint 型の 600 個のスパース列を持つテーブルがあるとします。 571 個の NULL 以外の列がある場合、ディスク上の合計サイズは 571 * 12 = 6852 バイトです。 追加の行のオーバーヘッドとスパース列ヘッダーを含めた場合、サイズは約 6895 バイトになります。 このページは、ディスク上で約 1124 バイトをまだ利用可能です。 これ場合、追加の列を正常に更新できるように思われます。 しかし、更新時は、"2*(NULL 以外のスパース列の数)" の追加のオーバーヘッドが発生します。 この例で追加のオーバーヘッド (2 * 571 = 1142 バイト) を含めると、ディスク上の行サイズは約 8037 バイトに増えます。 このサイズは、最大許容サイズの 8019 バイトを超えています。 すべての列は固定長データ型であるため、行外に出すことはできません。 その結果、更新は 576 エラーで失敗します。

スパース列の使用に関する制限

スパース列は、任意の SQL Server データ型にすることができ、他の列と同じように動作しますが、次の制限があります。

• スパース列は、NULL 値を許容する必要があり、ROWGUIDCOL または IDENTITY プロパティを持つことができません。 スパース列のデータ型を text、 ntext、 image、 timestamp、ユーザー定義データ型、 geometry、または geographyにすることはできません。また、スパース列には FILESTREAM 属性を指定できません。

• スパース列には既定値を設定できません。

• スパース列はルールにバインドできません。

• 計算列にスパース列を含めることはできますが、計算列を SPARSE とマークすることはできません。

• データ マスクは、スパース列で定義できますが、列セットの一部であるスパース列では定義できません。

• スパース列をクラスター化インデックスまたは一意の主キー インデックスに含めることはできません。 ただし、スパース列に定義された保存される計算列と保存されない計算列はどちらも、クラスター化キーに含めることができます。

• スパース列は、クラスター化インデックスまたはヒープのパーティション キーとして使用できません。 ただし、スパース列を非クラスター化インデックスのパーティション キーとして使用することはできます。

• テーブル変数およびテーブル値パラメーターで使用されるユーザー定義テーブル型に、スパース列を含めることはできません。

• スパース列は、データ圧縮と互換性がありません。 したがって、圧縮されたテーブルにスパース列を追加したり、スパース列を含むテーブルを圧縮したりすることはできません。

• スパースから非スパース、または非スパースからスパースに列を変更するには、列のストレージ形式を変更する必要があります。 SQL Server データベース エンジンでは、次の手順を使用してこの操作を実行します。

  1. 新しいストレージ サイズおよびストレージ形式でテーブルに新しい列を追加します。

  2. テーブル内の行ごとに、古い列に格納されている値を更新して新しい列にコピーします。

  3. 古い列をテーブル スキーマから削除します。

  4. 古い列で使用されていた領域を再利用するために、テーブルを再構築するか (クラスター化インデックスが存在しない場合)、クラスター化インデックスを再構築します。

  Note

  許容されている最大行サイズを行内のデータのサイズが超える場合、手順 2. が失敗する場合があります。 このサイズには、古い列に格納されているデータのサイズと、新しい列に格納されている更新されたデータのサイズが含まれます。 この制限値は、スパース列を含まないテーブルの場合は 8,060 バイト、スパース列を含むテーブルの場合は 8,018 バイトです。 このエラーは、条件を満たすすべての列が行外にプッシュされている場合でも発生します。

• 非スパース列をスパース列に変更すると、その列で NULL 以外の値に使用される領域が増えます。 行のサイズがその上限に近い場合は、操作が失敗することがあります。

スパース列をサポートする SQL Server のテクノロジ

ここでは、次に示す SQL Server のテクノロジでスパース列がどのようにサポートされるかを説明します。

• トランザクション レプリケーション

  トランザクション レプリケーションでは、スパース列はサポートされますが、スパース列と併用できる列セットはサポートされません。 列セットの詳細については、「 列セットの使用」を参照してください。

  SPARSE 属性のレプリケーションは、sp_addarticle または SQL Server Management Studio の [アーティクルのプロパティ] ダイアログ ボックスを使って指定されるスキーマ オプションによって決まります。 以前のバージョンの SQL Server では、スパース列はサポートされていません。 以前のバージョンにデータをレプリケートする必要がある場合は、SPARSE 属性をレプリケートしないように指定する必要があります。

  テーブルがパブリッシュされる場合は、テーブルに新しいスパース列を追加したり、既存の列のスパース プロパティを変更したりできません。 このような操作が必要な場合は、パブリケーションを削除して再作成します。

• マージ レプリケーション

  マージ レプリケーションでは、スパース列または列セットがサポートされません。

• 変更の追跡

  変更の追跡では、スパース列と列セットがサポートされます。 テーブルで列セットが更新されると、変更の追跡でその操作が行全体の更新として処理されます。 列セットの更新操作で更新された一連のスパース列を正確に特定するための詳細な変更追跡は行われません。 スパース列が DML ステートメントを通じて明示的に更新された場合は、その列に対する変更の追跡が通常どおりに機能し、変更された一連の列を正確に特定できます。

• 変更データ キャプチャ

  変更データ キャプチャでは、スパース列はサポートされますが、列セットはサポートされません。

• テーブルをコピーするとき、列のスパース プロパティは保持されません。

例

次の例では、ドキュメント テーブルに DocID 列と Title列のセットが共通で含まれています。 製造グループは、すべての製造ドキュメントに ProductionSpecification 列と ProductionLocation 列を必要とします。 マーケティング グループは、マーケティング ドキュメントに MarketingSurveyGroup 列を必要とします。 この例のコードでは、スパース列を使用するテーブルを作成し、そのテーブルに 2 つの行を挿入し、そのテーブルからデータを選択します。

Note

このテーブルは、簡単に表示して確認できるように、5 つの列のみで構成されています。 ANSI_NULL_DFLT_ON オプションが設定されている場合は、NULL 値を許容するようにスパース列を宣言しなくてもかまいません。 SET ANSI_DEFAULTS が ON の場合には、SET ANSI_NULL_DFLT_ON は有効になります。 ほとんどの接続プロバイダーで、ANSI_DEFAULTS は既定でオンになっています。 詳しくは、「SET ANSI_DEFAULTS」をご覧ください。

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStore  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL ) ;  
GO  
  
INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO  

テーブルからすべての列を選択すると、通常の結果セットが返されます。

SELECT * FROM DocumentStore ;  

結果セットは次のとおりです。

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

製造部門にマーケティング データは必要ないため、次のクエリに示すように、必要な列のみを返す列セットを使用します。

SELECT DocID, Title, ProductionSpecification, ProductionLocation   
FROM DocumentStore   
WHERE ProductionSpecification IS NOT NULL ;  

結果セットは次のとおりです。

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

関連項目

• 列セットの使用
• CREATE TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• sys.columns (Transact-SQL)