Использование наборов столбцовUse Column Sets

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server 2016 и более поздних версий даБаза данных SQL Azure даAzure Synapse Analytics (Хранилище данных SQL) даParallel Data Warehouse 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.

Определить набор столбцов можно с помощью ключевых слов <имя_набора_столбцов> FOR ALL_SPARSE_COLUMNS в инструкции CREATE TABLE или ALTER TABLE.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 ГБ.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. Поэтому в числовые столбцы вставляются значения 0, как это показано в следующем примере.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_variantUsing the sql_variant Data Type

Тип данных sql_variant может хранить несколько разных типов данных, например int, charи date.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 МасштабированиеScale
char, varchar, binarychar, 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
decimal, float, realdecimal, float, real НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable 1818 00
integer, bigint, tinyint, smallintinteger, 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
datetime, date, smalldatetimedatetime, date, smalldatetime НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable НеприменимоNot applicable
money, smallmoneymoney, 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.

  • Выполнение инструкции GRANT или REVOKE для разрешений SELECT, INSERT, UPDATE, DELETE и REFERENCES для столбца в наборе столбцов не распространяется на базовые столбцы-участники этого набора.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. В этом примере в таблицу вставляются две строки, а затем из таблицы выбираются данные.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. Вставка данных в таблицу с использованием имен разреженных столбцовInserting data to a table by using the names of the sparse columns

В следующих примерах в таблицу, созданную в примере А, вставляются две строки. В примерах используются имена разреженных столбцов; набор столбцов не упоминается.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. Вставка данных в таблицу с использованием имени набора столбцовInserting data to a table by using the name of the column set

В следующем примере в таблицу, созданную в примере А, вставляется третья строка. В этот раз имена разреженных столбцов не используются.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. Рассмотрение результатов для набора столбцов при выполнении инструкции 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. Рассмотрение результатов выбора набора столбцов с использованием его имени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. Рассмотрение результатов выбора разреженных столбцов с использованием их имен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. Обновление таблицы с помощью набора столбцов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