スパース列の使用Use Sparse Columns

適用対象: ○SQL Server ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

スパース列は、NULL 値用にストレージが最適化されている通常の列です。Sparse columns are ordinary columns that have an optimized storage for null values. スパース列によって、NULL 以外の値を取得するためのオーバーヘッドは増大しますが、NULL 値に必要となる領域は削減されます。Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. 少なくとも 20 ~ 40% の領域を削減できる場合は、スパース列の使用を検討してください。Consider using sparse columns when the space saved is at least 20 percent to 40 percent. スパース列および列セットを定義するには、 CREATE TABLE または ALTER TABLE ステートメントを使用します。Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

スパース列は、列セットおよびフィルター選択されたインデックスと併用できます。Sparse columns can be used with column sets and filtered indexes:

  • 列セットColumn sets

    INSERT、UPDATE、DELETE の各ステートメントは、スパース列を名前で参照できます。INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. ただし、テーブルのすべてのスパース列を 1 つの XML 列に結合して表示および操作することもできます。However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. この列を列セットと呼びます。This column is called a column set. 列セットの詳細については、「 列セットの使用」を参照してください。For more information about column sets, see Use Column Sets.

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

    スパース列は、NULL 値の行が多数あるため、フィルター選択されたインデックスに特に適しています。Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. スパース列でフィルター選択されたインデックスを使用すると、値が設定された行にのみインデックスを作成できます。A filtered index on a sparse column can index only the rows that have populated values. これにより、より小さく効率的なインデックスが作成されます。This creates a smaller and more efficient index. 詳細については、「 Create Filtered Indexes」を参照してください。For more information, see Create Filtered Indexes.

スパース列とフィルター選択されたインデックスを併用することで、 Windows SharePoint ServicesWindows SharePoint Servicesなどのアプリケーションでは、 SQL Server 2017SQL Server 2017を使用して多数のユーザー定義プロパティを効率よく格納およびアクセスできます。Sparse columns and filtered indexes enable applications, such as Windows SharePoint ServicesWindows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2017SQL Server 2017.

スパース列のプロパティProperties of Sparse Columns

スパース列には次の特性があります。Sparse columns have the following characteristics:

  • SQL Server データベース エンジンSQL Server Database Engine は、列定義で SPARSE キーワードを使用して、その列での値のストレージを最適化します。The SQL Server データベース エンジンSQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. このため、列値が NULL である行がテーブルに含まれている場合、その値はストレージを必要としません。Therefore, when the column value is NULL for any row in the table, the values require no storage.

  • スパース列を含んでいるテーブルのカタログ ビューは、一般的なテーブルのものと同じです。Catalog views for a table that has sparse columns are the same as for a typical table. sys.columns カタログ ビューは、テーブル内の各列の行で構成され、列セットが定義されていればそれを含んでいます。The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.

  • スパース列は、論理テーブルではなく、ストレージ層のプロパティです。Sparse columns are a property of the storage layer, rather than the logical table. したがって、SELECT...INTO ステートメントは、スパース列のプロパティを新しいテーブルにコピーしません。Therefore a SELECT...INTO statement does not copy over the sparse column property into a new table.

  • COLUMNS_UPDATED 関数は、DML アクションで更新されたすべての列を示す varbinary 値を返します。The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. COLUMNS_UPDATED 関数から返されるビットは次のとおりです。The bits that are returned by the COLUMNS_UPDATED function are as follows:

    • スパース列が明示的に更新された場合は、そのスパース列の対応するビットが 1 に設定され、列セットのビットが 1 に設定されます。When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.

    • 列セットが明示的に更新された場合は、列セットのビットが 1 に設定され、そのテーブル内のすべてのスパース列のビットが 1 に設定されます。When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.

    • 挿入操作では、すべてのビットが 1 に設定されます。For insert operations, all bits are set to 1.

    列セットの詳細については、「 列セットの使用」を参照してください。For more information about columns sets, see Use Column Sets.

次のデータ型は SPARSE と指定できません。The following data types cannot be specified as SPARSE:

geographygeography texttext
geometrygeometry timestamptimestamp
imageimage ユーザー定義データ型user-defined data types
ntextntext

領域を節約するためのデータ型別推定値Estimated Space Savings by Data Type

スパース列は、同一データが SPARSE とマークされていない場合に比べて、NULL 以外の値により多くのストレージ領域を必要とします。Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. 次の表は、各データ型の使用領域を示したものです。The following tables show the space usage for each data type. NULL の比率 列は、正味 40% の領域を節約するために必要な NULL データの割合を示します。The NULL Percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.

固定長データ型Fixed-Length Data Types

データ型Data type 非スパース バイト数Nonsparse bytes スパース バイト数Sparse bytes NULL の比率NULL percentage
bitbit 0.1250.125 55 98%98%
tinyinttinyint 11 55 86%86%
smallintsmallint 22 66 76%76%
intint 44 88 64%64%
bigintbigint 88 1212 52%52%
realreal 44 88 64%64%
floatfloat 88 1212 52%52%
smallmoneysmallmoney 44 88 64%64%
moneymoney 88 1212 52%52%
smalldatetimesmalldatetime 44 88 64%64%
datetimedatetime 88 1212 52%52%
uniqueidentifieruniqueidentifier 1616 2020 43%43%
datedate 33 77 69%69%

Precision-Dependent-Length データ型Precision-Dependent-Length Data Types

データ型Data type 非スパース バイト数Nonsparse bytes スパース バイト数Sparse bytes NULL の比率NULL percentage
datetime2(0)datetime2(0) 66 1010 57%57%
datetime2(7)datetime2(7) 88 1212 52%52%
time(0)time(0) 33 77 69%69%
time(7)time(7) 55 99 60%60%
datetimetoffset(0)datetimetoffset(0) 88 1212 52%52%
datetimetoffset (7)datetimetoffset (7) 1010 1414 49%49%
decimal/numeric(1,s)decimal/numeric(1,s) 55 99 60%60%
decimal/numeric(38,s)decimal/numeric(38,s) 1717 2121 42%42%
vardecimal(p,s)vardecimal(p,s) 控えめな推定値として decimal 型を使用してください。Use the decimal type as a conservative estimate.

Data-Dependent-Length データ型Data-Dependent-Length Data Types

データ型Data type 非スパース バイト数Nonsparse bytes スパース バイト数Sparse bytes NULL の比率NULL percentage
sql_variantsql_variant 基になるデータ型で異なります。Varies with the underlying data type
varchar または charvarchar or char 2*2* 4*4* 60%60%
nvarchar または ncharnvarchar or nchar 2*2* 4*+4*+ 60%60%
varbinary または binaryvarbinary or binary 2*2* 4*4* 60%60%
xmlxml 2*2* 4*4* 60%60%
hierarchyidhierarchyid 2*2* 4*4* 60%60%

* 長さは、型に含まれているデータの平均に 2 バイトまたは 4 バイトを加えた長さに等しくなります。*The length is equal to the average of the data that is contained in the type, plus 2 or 4 bytes.

スパース列の更新に必要なインメモリ オーバーヘッドIn-Memory Overhead Required for Updates to Sparse Columns

スパース列を含むテーブルをデザインする場合は、行を更新するときにテーブル内の NULL 以外のスパース列ごとに追加の 2 バイトが必要になることに注意してください。When designing tables with sparse columns, keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated. この追加のメモリ要件により、(このメモリ オーバーヘッドを含む) 合計行サイズが 8019 を超え、列を行外に出すことができないと、更新がエラー 576 で予期せずに失敗する可能性があります。As a result of this additional memory requirement, updates can fail unexpectedly with error 576 when the total row size, including this memory overhead, exceeds 8019, and no columns can be pushed off the row.

たとえば、bigint 型の 600 個のスパース列を持つテーブルがあるとします。Consider the example of a table that has 600 sparse columns of type bigint. 571 個の NULL 以外の列がある場合、ディスク上の合計サイズは 571 * 12 = 6852 バイトです。If there are 571 non-null columns, then the total size on disk is 571 * 12 = 6852 bytes. 追加の行のオーバーヘッドとスパース列ヘッダーを含めた場合、サイズは約 6895 バイトになります。After including additional row overhead and the sparse column header, this increases to around 6895 bytes. このページは、ディスク上で約 1124 バイトをまだ利用可能です。The page still has around 1124 bytes available on disk. これ場合、追加の列を正常に更新できるように思われます。This can give the impression that additional columns can be updated successfully. しかし、更新時は、"2*(NULL 以外のスパース列の数)" で求められる追加のオーバーヘッドが発生します。However, during the update, there is additional overhead in memory which is 2*(number of non-null sparse columns). この例で追加のオーバーヘッド (2 * 571 = 1142 バイト) を含めると、ディスク上の行サイズは約 8037 バイトに増えます。In this example, including the additional overhead - 2 * 571 = 1142 bytes - increases the row size on disk to around 8037 bytes. このサイズは、最大許容サイズの 8019 バイトを超えています。This size exceeds the maximum allowed size of 8019 bytes. すべての列は固定長データ型であるため、行外に出すことはできません。Since all the columns are fixed-length data types, they cannot be pushed off the row. その結果、更新は 576 エラーで失敗します。As a result, the update fails with the 576 error.

スパース列の使用に関する制限Restrictions for Using Sparse Columns

スパース列は、任意の SQL ServerSQL Server データ型にすることができ、他の列と同じように動作しますが、次の制限があります。Sparse columns can be of any SQL ServerSQL Server data type and behave like any other column with the following restrictions:

  • スパース列は、NULL 値を許容する必要があり、ROWGUIDCOL または IDENTITY プロパティを持つことができません。A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. スパース列のデータ型を textntextimagetimestamp、ユーザー定義データ型、 geometry、または geographyにすることはできません。また、スパース列には FILESTREAM 属性を指定できません。A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.

  • スパース列には既定値を設定できません。A sparse column cannot have a default value.

  • スパース列はルールにバインドできません。A sparse column cannot be bound to a rule.

  • 計算列にスパース列を含めることはできますが、計算列を SPARSE とマークすることはできません。Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.

  • データ マスクは、スパース列で定義できますが、列セットの一部であるスパース列では定義できません。A data mask can be defined on a sparse column, but not on a sparse column that is part of a column set.

  • スパース列をクラスター化インデックスまたは一意の主キー インデックスに含めることはできません。A sparse column cannot be part of a clustered index or a unique primary key index. ただし、スパース列に定義された保存される計算列と保存されない計算列は、クラスター化キーに含めることができます。However, both persisted and nonpersisted computed columns that are defined on sparse columns can be part of a clustered key.

  • スパース列は、クラスター化インデックスまたはヒープのパーティション キーとして使用できません。A sparse column cannot be used as a partition key of a clustered index or heap. ただし、スパース列を非クラスター化インデックスのパーティション キーとして使用することはできます。However, a sparse column can be used as the partition key of a nonclustered index.

  • テーブル変数およびテーブル値パラメーターで使用されるユーザー定義テーブル型に、スパース列を含めることはできません。A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.

  • スパース列は、データ圧縮と互換性がありません。Sparse columns are incompatible with data compression. したがって、圧縮されたテーブルにスパース列を追加したり、スパース列を含むテーブルを圧縮したりすることはできません。Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.

  • スパースから非スパース、または非スパースからスパースに列を変更するには、列のストレージ形式を変更する必要があります。Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column. SQL Server データベース エンジンでは、次の手順を使用してこの操作を実行します。The SQL Server Database Engine uses the following procedure to accomplish this change:

    1. 新しいストレージ サイズおよびストレージ形式でテーブルに新しい列を追加します。Adds a new column to the table in the new storage size and format.

    2. テーブル内の行ごとに、古い列に格納されている値を更新して新しい列にコピーします。For each row in the table, updates and copies the value stored in the old column to the new column.

    3. 古い列をテーブル スキーマから削除します。Removes the old column from the table schema.

    4. 古い列で使用されていた領域を再利用するために、テーブルを再構築するか (クラスター化インデックスが存在しない場合)、クラスター化インデックスを再構築します。Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

    注意

    許容されている最大行サイズを行内のデータのサイズが超える場合、手順 2. が失敗する場合があります。Step 2 can fail when the size of the data in the row exceeds the maximum allowable row size. このサイズには、古い列に格納されているデータのサイズと、新しい列に格納されている更新されたデータのサイズが含まれます。This size includes the size of the data stored in the old column and the updated data stored in the new column. この制限値は、スパース列を含まないテーブルの場合は 8,060 バイト、スパース列を含むテーブルの場合は 8,018 バイトです。This limit is 8060 bytes for tables that do not contain any sparse columns or 8018 bytes for tables that contain sparse columns. このエラーは、条件を満たすすべての列が行外にプッシュされている場合でも発生します。This error can occur even if all eligible columns have been pushed off-row.

  • 非スパース列をスパース列に変更すると、その列で NULL 以外の値に使用される領域が増えます。When you change a non-sparse column to a sparse column, the sparse column will consume more space for non-null values. 行のサイズがその上限に近い場合は、操作が失敗することがあります。When a row is close to the maximum row size limit, the operation can fail.

スパース列をサポートする SQL Server のテクノロジSQL Server Technologies That Support Sparse Columns

ここでは、次に示す SQL ServerSQL Server のテクノロジでスパース列がどのようにサポートされるかを説明します。This section describes how sparse columns are supported in the following SQL ServerSQL Server technologies:

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

    トランザクション レプリケーションでは、スパース列はサポートされますが、スパース列と併用できる列セットはサポートされません。Transactional replication supports sparse columns, but it does not support column sets, which can be used with sparse columns. 列セットの詳細については、「 列セットの使用」を参照してください。For more information about column sets, see Use Column Sets.

    SPARSE 属性のレプリケーションは、 sp_addarticle または [アーティクルのプロパティ] SQL Server Management StudioSQL Server Management Studioダイアログ ボックスで指定されるスキーマ オプションによって決まります。The replication of the SPARSE attribute is determined by a schema option that is specified by using sp_addarticle or by using the Article Properties dialog box in SQL Server Management StudioSQL Server Management Studio. 以前のバージョンの SQL ServerSQL Server では、スパース列がサポートされません。Earlier versions of SQL ServerSQL Server do not support sparse columns. 以前のバージョンにデータをレプリケートする必要がある場合は、SPARSE 属性をレプリケートしないように指定する必要があります。If you must replicate data to an earlier version, specify that the SPARSE attribute should not be replicated.

    テーブルがパブリッシュされる場合は、テーブルに新しいスパース列を追加したり、既存の列のスパース プロパティを変更したりできません。For tables that are published, you cannot add any new sparse columns to a table or change the sparse property of an existing column. このような操作が必要な場合は、パブリケーションを削除して再作成します。If such an operation is required, drop and re-create the publication.

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

    マージ レプリケーションでは、スパース列または列セットがサポートされません。Merge replication does not support sparse columns or column sets.

  • 変更の追跡Change tracking

    変更の追跡では、スパース列と列セットがサポートされます。Change tracking supports sparse columns and column sets. テーブルで列セットが更新されると、変更の追跡でその操作が行全体の更新として処理されます。When a column set is updated in a table, change tracking treats this as an update to the whole row. 列セットの更新操作で更新された一連のスパース列を正確に特定するための詳細な変更追跡は行われません。No detailed change tracking is provided to obtain the exact set of sparse columns that are updated through the column set update operation. スパース列が DML ステートメントを通じて明示的に更新された場合は、その列に対する変更の追跡が通常どおりに機能し、変更された一連の列を正確に特定できます。If the sparse columns are updated explicitly through a DML statement, change tracking on them will work ordinarily and can identify the exact set of changed columns.

  • 変更データ キャプチャChange data capture

    変更データ キャプチャでは、スパース列はサポートされますが、列セットはサポートされません。Change data capture supports sparse columns, but it does not support column sets.

  • テーブルをコピーするとき、列のスパース プロパティは保持されません。The sparse property of a column is not preserved when the table is copied.

使用例Examples

次の例では、ドキュメント テーブルに DocID 列と Title列のセットが共通で含まれています。In this example, a document table contains a common set that has the columns DocID and Title. 製造グループは、すべての製造ドキュメントに ProductionSpecification 列と ProductionLocation 列を必要とします。The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. マーケティング グループは、マーケティング ドキュメントに MarketingSurveyGroup 列を必要とします。The Marketing group wants a MarketingSurveyGroup column for marketing documents. この例のコードでは、スパース列を使用するテーブルを作成し、そのテーブルに 2 つの行を挿入し、そのテーブルからデータを選択します。The code in this example creates a table that uses sparse columns, inserts two rows into the table, and then selects data from the table.

注意

このテーブルは、簡単に表示して確認できるように、5 つの列のみで構成されています。This table has only five columns to make it easier to display and read. ANSI_NULL_DFLT_ON オプションが設定されている場合は、NULL 値を許容するようにスパース列を宣言しなくてもかまいません。Declaring the sparse columns to be nullable is optional if the ANSI_NULL_DFLT_ON option is set.

USE AdventureWorks2012;  
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  

テーブルからすべての列を選択すると、通常の結果セットが返されます。To select all the columns from the table returns an ordinary result set.

SELECT * FROM DocumentStore ;  

以下に結果セットを示します。Here is the result set.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

製造部門にマーケティング データは必要ないため、次のクエリに示すように、必要な列のみを返す列セットを使用します。Because the Production department is not interested in the marketing data, they want to use a column list that returns only columns of interest, as shown in the following query.

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

以下に結果セットを示します。Here is the result set.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

参照See Also

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