列セットの使用Use Column Sets

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

スパース列を使用するテーブルでは、テーブル内のすべてのスパース列を返すための列セットを指定できます。Tables that use sparse columns can designate a column set to return all sparse columns in the table. 列セットは、型指定されていない XML 表記であり、テーブルのすべてのスパース列を 1 つにまとめて構造化した出力です。A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output. 列セットは、テーブルに物理的に保存されないという点で計算列に似ています。A column set is like a calculated column in that the column set is not physically stored in the table. 計算列と異なるのは、列セットが直接更新できる点です。A column set differs from a calculated column in that the column set is directly updatable.

テーブルに多数の列があり、その個別操作が煩雑である場合は、列セットの使用を検討してください。You should consider using column sets when the number of columns in a table is large, and operating on them individually is cumbersome. 多数の列を含むテーブルで列セットを使用してデータの選択や挿入を行うと、アプリケーションのパフォーマンスがある程度向上する場合があります。Applications might see some performance improvement when they select and insert data by using column sets on tables that have lots of columns. ただし、テーブル内の列に多数のインデックスが定義されている場合は、列セットのパフォーマンスが低下することがあります。However, the performance of column sets can be reduced when many indexes are defined on the columns in the table. これは、実行プランに必要なメモリ容量が増えるためです。This is because the amount of memory that is required for an execution plan increases.

列セットを定義するには、CREATE TABLE または ALTER TABLE ステートメントで <column_set_name> FOR ALL_SPARSE_COLUMNS キーワードを使用します。To define a column set, use the <column_set_name> FOR ALL_SPARSE_COLUMNS keywords in theCREATE TABLE or ALTER TABLE statements.

列セットの使用に関するガイドラインGuidelines for Using Column Sets

列セットを使用する場合は、次のガイドラインを考慮してください。When you use column sets, consider the following guidelines:

  • スパース列と列セットは、同じステートメントの一部として追加できます。Sparse columns and a column set can be added as part of the same statement.

  • 既にスパース列が含まれているテーブルには列セットを追加できません。A column set cannot be added to a table if that table already contains sparse columns.

  • 列セットは変更できません。The column set cannot be changed. 列セットを変更するには、スパース列と列セットを削除してから再作成する必要があります。To change a column set, you must delete and re-create the sparse columns and the column set.

  • スパース列を含んでいないテーブルには列セットを追加できます。A column set can be added to a table that does not include any sparse columns. テーブルに後からスパース列が追加された場合は、列セット内に配置されます。If sparse columns are later added to the table, they will appear in the column set.

  • 1 つのテーブルに作成できる列セットは 1 つだけです。Only one column set is allowed per table.

  • 列セットはオプションであり、スパース列を使用するために必須ではありません。A column set is optional and is not required to use sparse columns.

  • 列セットには制約や既定値を定義できません。Constraints or default values cannot be defined on a column set.

  • 計算列に列セットの列を含めることはできません。Computed columns cannot contain column set columns.

  • 列セットを含むテーブルでは、分散クエリがサポートされません。Distributed queries are not supported on tables that contain column sets.

  • レプリケーションでは列セットはサポートされません。Replication does not support column sets.

  • 変更データ キャプチャでは列セットはサポートされません。Change data capture does not support column sets.

  • 列セットは、どの種類のインデックスにも組み込むことができません。A column set cannot be part of any kind of index. これには、XML インデックス、フルテキスト インデックス、およびインデックス付きビューが含まれます。This includes XML indexes, full-text indexes, and indexed views. 列セットをインデックスの付加列として追加することはできません。A column set cannot be added as an included column in any index.

  • フィルター選択されたインデックスまたはフィルター選択された統計情報のフィルター式では、列セットを使用できません。A column set cannot be used in the filter expression of a filtered index or filtered statistics.

  • ビューに含まれている列セットは、そのビューで XML 列として表示されます。When a view includes a column set, the column set appears in the view as an XML column.

  • インデックス付きビューの定義に列セットを含めることはできません。A column set cannot be included in an indexed view definition.

  • 列セットを含むテーブルを含んでいるパーティション ビューは、そのビューでスパース列が名前で指定されている場合に更新できます。Partitioned views that include tables that contain column sets are updatable when the partitioned view specifies the sparse columns by name. 列セットを参照しているパーティション ビューは更新できません。A partitioned view is not updatable when it references the column set.

  • 列セットを参照するクエリ通知は許可されません。Query notifications that refer to column sets are not permitted.

  • XML データのサイズは 2 GB に制限されます。XML data has a size limit of 2 GB. 行内の NULL ではないすべてのスパース列のデータの合計がこの制限を超えると、クエリまたは DML 操作でエラーが発生します。If the combined data of all the nonnull sparse columns in a row exceeds this limit, the query or DML operation will produce an error.

  • COLUMNS_UPDATED 関数から返されるデータの詳細については、「 スパース列の使用」を参照してください。For information about the data that is returned by the COLUMNS_UPDATED function, see Use Sparse Columns.

列セットからのデータの選択に関するガイドラインGuidelines for Selecting Data from a Column Set

列セットからデータを選択する場合は、次のガイドラインを考慮してください。Consider the following guidelines for selecting data from a column set:

  • 列セットは、概念的には更新可能な XML 計算列の一種であり、基になる一連のリレーショナル列を単一の XML 表記に集約します。Conceptually, a column set is a type of updatable, computed XML column that aggregates a set of underlying relational columns into a single XML representation. 列セットは、ALL_SPARSE_COLUMNS プロパティのみをサポートします。The column set only supports the ALL_SPARSE_COLUMNS property. このプロパティは、特定の行のすべてのスパース列から NULL 以外の値を集計するために使用されます。This property is used to aggregate all nonnull values from all sparse columns for a particular row.

  • SQL Server Management StudioSQL Server Management Studio テーブル エディターでは、列セットは編集可能な XML フィールドとして表示されます。In the SQL Server Management StudioSQL Server Management Studio table editor, column sets are displayed as an editable XML field. 列セットは次の形式で定義します。Define column sets in the format:

    <column_name_1>value1</column_name_1><column_name_2>value2</column_name_2>...  
    

    列セットの値の例を次に示します。Examples of column set values are as follows:

    • <sparseProp1>10</sparseProp1><sparseProp3>20</sparseProp3>

    • <DocTitle>Bicycle Parts List</DocTitle><Region>West</Region>

  • NULL 値が格納されたスパース列は、列セットの XML 表記で省略されます。Sparse columns that contain null values are omitted from the XML representation for the column set.

警告

列セットを追加すると、SELECT * クエリの動作が変わります。Adding a column set changes the behavior of SELECT * queries. クエリから列セットが XML 列として返されるようになり、個々のスパース列は返されなくなります。The query will return the column set as an XML column and not return the individual sparse columns. スキーマ設計者とソフトウェア開発者は、既存のアプリケーションが動作不能とならないように注意する必要があります。Schema designers and software developers must be careful not to break existing applications.

列セットのデータの挿入または変更Inserting or Modifying Data in a Column Set

スパース列のデータ操作を実行するには、個々の列の名前を使用するか、または列セットの名前を参照し、列セットの XML 形式を使用して列セットの値を指定します。Data manipulation of a sparse column can be performed by using the name of the individual columns, or by referencing the name of the column set and specifying the values of the column set by using the XML format of the column set. XML 列では、スパース列を任意の順序で配置できます。Sparse columns can appear in any order in the XML column.

XML 列セットを使用してスパース列の値を挿入または更新すると、基になるスパース列に挿入された値が xml データ型から暗黙的に変換されます。When sparse column values are inserted or updated by using the XML column set, the values that are inserted into the underlying sparse columns are implicitly converted from the xml data type. 数値列の場合、数値列の XML に含まれる空白値は空白の文字列に変換されます。In the case of numeric columns, a blank value in the XML for the numeric column converts to an empty string. これにより、次の例のように、数値列にゼロが挿入されます。This causes a zero to be inserted into the numeric column as shown in the following example.

CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS);  
GO  
INSERT t(cs) VALUES ('<i/>');  
GO  
SELECT i FROM t;  
GO  

この例では、 i列に値が指定されていませんが、値 0 が挿入されています。In this example, no value was specified for the column i, but the value 0 was inserted.

sql_variant データ型の使用Using the sql_variant Data Type

sql_variant データ型には、 intchardateなどの種類の異なる複数のデータ型を格納できます。The sql_variant date type can store multiple different data types, such as int, char, and date. 列セットは、 sql_variant 値に関連付けられている小数点以下桁数、有効桁数、ロケール情報などのデータ型情報を、生成された XML 列に属性として出力します。Column sets output the data type information such as scale, precision, and locale information that is associated with a sql_variant value as attributes in the generated XML column. これらの属性を、列セットでの挿入または更新操作の入力としてカスタム生成 XML ステートメントで指定しようとすると、一部の属性が必須となり、一部の属性に既定値が割り当てられます。If you try to provide these attributes in a custom-generated XML statement as an input for an insert or update operation on a column set, some of these attributes are required and some of them are assigned a default value. 値が指定されなかったときにサーバーで生成されるデータ型と既定値の一覧を次の表に示します。The following table lists the data types and the default values that the server generates when the value is not provided.

データ型Data type localeID*localeID* sqlCompareOptionssqlCompareOptions sqlCollationVersionsqlCollationVersion SqlSortIdSqlSortId 最大の長さMaximum length 有効桁数Precision ScaleScale
charvarcharbinarychar, varchar, binary -1-1 'Default''Default' 00 00 80008000 なし**Not applicable** 適用なしNot applicable
nvarcharnvarchar -1-1 'Default''Default' 00 00 40004000 適用なしNot applicable 適用なしNot applicable
decimalfloatrealdecimal, float, real 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 1818 00
intbiginttinyintsmallintinteger, bigint, tinyint, smallint 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable
datetime2datetime2 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 77
datetimeoffsetdatetime offset 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 77
datetimedatesmalldatetimedatetime, date, smalldatetime 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable
moneysmallmoneymoney, smallmoney 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable
timetime 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 適用なしNot applicable 77

* localeID -1 は既定のロケールを意味します。* localeID -1 means the default locale. 英語ロケールは 1033 です。The English locale is 1033.

** なし = 列セットでの選択操作時に、対象の属性に対して値が出力されません。** Not applicable = No values are output for these attributes during a select operation on the column set. 挿入または更新操作で列セットに対して指定された XML 表記で呼び出し元がこの属性に値を指定すると、エラーが発生します。Generates an error when a value is specified for this attribute by the caller in the XML representation provided for a column set in an insert or update operation.

SecuritySecurity

列セットのセキュリティ モデルは、テーブルと列の間に介在するセキュリティ モデルと同じように機能します。The security model for a column set works similar to the security model that exists between table and columns. 列セットはミニテーブルとして視覚化できます。選択操作は、このミニテーブルに対する SELECT * 操作と同様です。Column sets can be visualized as a minitable and a select operation is like a SELECT * operation on this minitable. ただし、列セットとスパース列は、厳密なコンテナーではなくグループ化の関係にあります。But, the relationship between column set to sparse columns is a grouping relationship instead of strictly a container. セキュリティ モデルでは、列セットの列に対してセキュリティがチェックされ、基になるスパース列で DENY 操作が適用されます。The security model checks the security on the column set column, and honors the DENY operations on the underlying sparse columns. セキュリティ モデルには、これ以外に次のような特性があります。Additional characteristics of the security model are as follows:

  • 列セットの列に対し、テーブル内の他の列と同様に、セキュリティ権限を与えたり取り消したりすることができます。Security permissions can be granted and revoked from the column set column, similar to any other column in the table.

  • 列セットの列に対して SELECT、INSERT、UPDATE、DELETE、および REFERENCES 権限の GRANT または REVOKE を実行しても、そのセットの基になるメンバー列には反映されません。A GRANT or REVOKE of SELECT, INSERT, UPDATE, DELETE, and REFERENCES permission on a column set column does not propagate to the underlying member columns of that set. これは、列セットの列を使用する場合にのみ当てはまります。It applies only to the usage of the column set column. 列セットに対する DENY 権限は、テーブルの基になるスパース列に反映されます。DENY permission on a column set does propagate to the underlying sparse columns of the table.

  • 列セットの列に対して SELECT、INSERT、UPDATE、DELETE の各ステートメントを実行するには、ユーザーはその列セットの列についての対応する権限、およびテーブル内のすべてのスパース列についての対応する権限を持っている必要があります。Executing SELECT, INSERT, UPDATE, and DELETE statements on the column set column require that the user has corresponding permissions on the column set column, and also the corresponding permission on all the sparse columns in the table. 列セットはテーブル内のすべてのスパース列を表すため、すべてのスパース列についての権限が必要になります。これには、変更の対象ではないスパース列も含まれます。Because the column set represents all the sparse columns in the table, you must have permission on all the sparse columns, and this includes sparse columns that you might not be changing.

  • スパース列または列セットに対して REVOKE ステートメントを実行すると、セキュリティは、既定でその親オブジェクトのセキュリティに設定されます。Executing a REVOKE statement on a sparse column or column set defaults the security to their parent object.

使用例Examples

次の例では、ドキュメント テーブルに DocID 列と Title列のセットが共通で含まれています。In the following examples, a document table contains the common set of 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.

A.A. 列セットを含むテーブルを作成するCreating a table that has a column set

次の例では、スパース列を使用し、列セット SpecialPurposeColumnsを含むテーブルを作成します。The following example creates the table that uses sparse columns and includes the column set SpecialPurposeColumns. 例では、テーブルに 2 つの行を挿入した後に、テーブルからデータを選択します。The example 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.

USE AdventureWorks2012;  
GO  
  
CREATE TABLE DocumentStoreWithColumnSet  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL,  
     MarketingProgramID int SPARSE NULL,  
     SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);  
GO  

B.B. スパース列の名前を使用してテーブルにデータを挿入するInserting data to a table by using the names of the sparse columns

次の例では、例 A で作成したテーブルに 2 つの行を挿入します。列セットは参照せず、スパース列の名前を使用します。The following examples insert two rows into the table that is created in example A. The examples use the names of the sparse columns and do not reference the column set.

INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO  

C.C. 列セットの名前を使用してテーブルにデータを挿入するInserting data to a table by using the name of the column set

次の例では、例 A で作成したテーブルに 3 つ目の行を挿入します。今回はスパース列の名前を使用しません。The following example inserts a third row into the table that is created in example A. This time the names of the sparse columns are not used. 代わりに、列セットの名前を使用し、挿入操作によって 4 つのスパース列の 2 つに XML 形式で値を挿入します。Instead, the name of the column set is used, and the insert provides the values for two of the four sparse columns in XML format.

INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)  
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>');  
GO  

D.D. SELECT * を使用したときの列セットの結果を確認するObserving the results of a column set when SELECT * is used

次の例では、列セットを含むテーブルからすべての列を選択します。The following example selects all the columns from the table that contains a column set. この操作で、スパース列の値の組み合わせを含んだ XML 列が返されます。It returns an XML column with the combined values of the sparse columns. スパース列が個別に返されることはありません。It does not return the sparse columns individually.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;  

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

DocID Title SpecialPurposeColumns

1 Tire Spec 1 <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>

2 Survey 2142 <MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>

3 Tire Spec 2 <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

E.E. 列セットを名前で選択した場合の結果を確認するObserving the results of selecting the column set by name

製造部門にマーケティング データは必要ないため、この例では WHERE 句を追加して出力を制限します。Because the Production department is not interested in the marketing data, this example adds a WHERE clause to restrict the output. 例では、列セットの名前が使用されています。The example uses the name of the column set.

SELECT DocID, Title, SpecialPurposeColumns  
FROM DocumentStoreWithColumnSet  
WHERE ProductionSpecification IS NOT NULL ;  

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

DocID Title SpecialPurposeColumns

1 Tire Spec 1 <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>

3 Tire Spec 2 <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

F.F. スパース列を名前で選択した場合の結果を確認するObserving the results of selecting sparse columns by name

テーブルに列セットが含まれている場合でも、次の例のように個々の列名を使用してテーブルに対してクエリを実行できます。When a table contains a column set, you can still query the table by using the individual column names as shown in the following example.

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

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

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

3 Tire Spec 2 AXW9R411 38

G.G. 列セットを使用してテーブルを更新するUpdating a table by using a column set

次の例では、3 番目のレコードを、その行で使用されている 2 つのスパース列の新しい値で更新します。The following example updates the third record with new values for both sparse columns that are used by that row.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'  
WHERE DocID = 3 ;  
GO  

重要

UPDATE ステートメントで列セットを使用すると、テーブル内のすべてのスパース列が更新されます。An UPDATE statement that uses a column set updates all the sparse columns in the table. 参照されていないスパース列は、NULL に更新されます。Sparse columns that are not referenced are updated to NULL.

次の例では、3 番目のレコードを更新しますが、作成された 2 つの列の 1 つにのみ値を指定します。The following example updates the third record, but only specifies the value of one of the two populated columns. 2 番目の列である ProductionLocationUPDATE ステートメントに含まれず、NULL に更新されます。The second column ProductionLocation is not included in the UPDATE statement and is updated to NULL.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'  
WHERE DocID = 3 ;  
GO  

参照See Also

スパース列の使用Use Sparse Columns