使用資料行集Use Column Sets

適用於: 是SQL Server 2016 與更新版本是Azure SQL Database是Azure Synapse Analytics (SQL DW)是平行處理資料倉儲APPLIES TO: YesSQL Server 2016 and later YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) YesParallel Data Warehouse

使用疏鬆資料行的資料表可以指定資料行集,以傳回資料表中的所有疏鬆資料行。Tables that use sparse columns can designate a column set to return all sparse columns in the table. 資料行集是不具類型的 XML 表示,可將資料表的所有疏鬆資料行結合到結構化輸出中。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 TABLEALTER TABLE 陳述式中使用 <資料行集名稱> FOR ALL_SPARSE_COLUMNS 關鍵字。To define a column set, use the <column_set_name> FOR ALL_SPARSE_COLUMNS keywords in the CREATE 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.

  • 一個資料表只能有一個資料行集。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>

  • 資料行集的 XML 表示中會省略包含 null 值的疏鬆資料行。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 資料類型可以儲存多種不同的資料類型,例如 intchardateThe 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 PrecisionPrecision 調整Scale
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
integerbiginttinyintsmallintinteger, 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
datetime offsetdatetime 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.

安全性Security

資料行集之安全性模型的運作方式,類似於資料表和資料行之間存在的安全性模型。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

在下列範例中,文件資料表包含常用的一組 DocIDTitle資料行。In the following examples, a document table contains the common set of columns DocID and Title. 生產小組想要所有生產文件的 ProductionSpecificationProductionLocation 資料行。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

下列範例會建立一個資料表,此資料表將使用疏鬆資料行,並包括資料行集 SpecialPurposeColumnsThe following example creates the table that uses sparse columns and includes the column set SpecialPurposeColumns. 此範例會將兩個資料列插入資料表中,然後選取資料表中的資料。The example inserts two rows into the table, and then selects data from the table.

注意

此資料表只有五個資料行,以方便顯示及閱讀。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 建立的資料表中。此範例會使用疏鬆資料行的名稱,而且不會參考資料行集。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 建立的資料表中。這次不會使用疏鬆資料行的名稱,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. 而是使用資料行集的名稱,而且插入作業會提供四個疏鬆資料行其中兩個的值 (使用 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

下列範例會使用該資料列所用之兩個疏鬆資料行的新值來更新第三筆記錄。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.

下列範例會更新第三筆記錄,但是只會指定兩個已填入之資料行其中一個的值。The following example updates the third record, but only specifies the value of one of the two populated columns. 第二個資料行 ProductionLocation 不會併入 UPDATE 陳述式中,而且會更新為 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