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

В таблицах, использующих разреженные столбцы, можно назначить набор столбцов, который будет возвращать все разреженные столбцы в таблице. Набор столбцов — это нетипизированное XML-представление, которое объединяет на выходе все разреженные столбцы таблицы в структурированном виде. Набор столбцов похож на вычисляемые столбцы тем, что набор столбцов физически не хранится в таблице. Набор столбцов отличается от вычисляемого столбца тем, что он может быть напрямую обновлен.

Наборы столбцов следует использовать в том случае, если в таблице существует большое число столбцов и работать с ними по отдельности неудобно. У приложений может возрасти производительность, если они будут выбирать и вставлять данные в таблицы, имеющие много столбцов, с помощью наборов столбцов. Однако производительность наборов столбцов может уменьшиться, если для столбцов в таблице было определено большое количество индексов. Это происходит из-за увеличения объема памяти, необходимого для плана выполнения.

Чтобы определить набор столбцов, в инструкции CREATE TABLE или ALTER TABLE следует использовать ключевые слова <имя_набора_столбцов> FOR ALL_SPARSE_COLUMNS.

Рекомендации по использованию наборов столбцов

При использовании наборов столбцов следует учитывать следующие рекомендации.

• Разреженные столбцы и набор столбцов могут быть созданы в рамках одной и той же инструкции.

• Набор столбцов нельзя изменять. Чтобы изменить набор столбцов, его нужно удалить и создать повторно.

• Набор столбцов не может быть добавлен в таблицу, если в ней уже содержатся разреженные столбцы.

• Набор столбцов может быть добавлен в таблицу, если в ней нет разреженных столбцов. Если впоследствии в таблицу будут добавлены разреженные столбцы, они появятся в наборе столбцов.

• В таблице может содержаться только один набор столбцов.

• Набор столбцов является дополнительной функцией, он не требуется для использования разреженных столбцов.

• Для набора столбцов нельзя определить ограничения или значения по умолчанию.

• Вычисляемые столбцы не могут содержать столбцы набора столбцов.

• Распределенные запросы не поддерживаются в таблицах, содержащих наборы столбцов.

• Репликация не поддерживает наборы столбцов.

• Система отслеживания измененных данных не поддерживает наборы столбцов.

• Набор столбцов не может быть частью никакого вида индексов. Это касается XML-индексов, полнотекстовых индексов и индексированных представлений. Набор столбцов не может быть добавлен как включенный столбец в любой индекс.

• Набор столбцов не может быть использован в критерии фильтра фильтруемого индекса или статистике фильтрации.

• Если представление содержит набор столбцов, в представлении он будет отображен как XML-столбец.

• Набор столбцов не может быть включен в определение индексированного представления.

• Секционированные представления, включающие таблицы, в которых содержатся наборы столбцов, могут быть обновлены, если секционированные представления упоминают разреженные столбцы по именам. Секционированное представление не может быть обновлено, если оно ссылается на набор столбцов.

• Не допускается использование уведомлений о запросах, ссылающихся на наборы столбцов.

• Предел размера XML-данных — 2 ГБ. Если сумма данных в строке во всех разреженных столбцах, содержащих значения, отличные от значений NULL, превышает этот предел, запрос или операция DML выдаст ошибку.

• Сведения о данных, возвращаемых функцией COLUMNS_UPDATED, см. в разделе Использование разреженных столбцов.

Рекомендации по выбору данных из набора столбцов

Следует учитывать следующие рекомендации при выборе данных из набора столбцов.

• Фактически, набор столбцов — это тип обновляемого, вычисляемого XML-столбца, в котором набор базовых реляционных столбцов собирается в единое XML-представление. Набор столбцов поддерживает только свойство ALL_SPARSE_COLUMNS. Это свойство используется для сбора всех значений, отличных от значения NULL, из всех разреженных столбцов в определенной строке.

• В редакторе таблиц среды SQL Server Management Studio наборы столбцов отображаются как изменяемые XML-поля. Наборы столбцов определяются с помощью следующего формата:

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

  Далее приводятся примеры значений набора столбцов:

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

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

• Разреженные столбцы, содержащие значения NULL, не включаются в XML-представление набора столбцов.

Внимание!
Добавление набора столбцов изменяет поведение запросов SELECT *. Запрос будет возвращать набор столбцов как XML-столбец, а не как отдельные разреженные столбцы. Разработчики схем и приложений должны учитывать это, чтобы не нарушить работу существующих приложений.

Вставка или изменение данных в наборе столбцов

Управлять данными в разреженных столбцах можно с помощью имен индивидуальных столбцов либо ссылаясь на имя набора столбцов и указывая значения набора столбцов, используя XML-формат набора столбцов. Разреженные столбцы могут быть расположены в XML-столбце в любом порядке.

При вставке или обновлении значений разреженных столбцов с помощью набора XML-столбцов производится неявное преобразование значений, вставляемых в лежащие в основе разреженные столбцы, из типа данных xml. Для числовых столбцов пустые значения в XML-столбцах преобразуются в пустые строки. Поэтому в числовые столбцы вставляются значения 0, как это показано в следующем примере.

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.

Использование типа данных sql_variant

Тип данных sql_variant может хранить несколько разных типов данных, например int, char и date. Наборы столбцов выводят сведения о типе данных (например, масштаб, точность или сведения о языковом стандарте), связанном со значением sql_variant, в виде атрибутов в формируемом XML-столбце. Если нужно предоставить эти атрибуты в сформированной пользователем XML-инструкции в качестве входных данных для операции вставки или обновления в наборе столбцов, то некоторые из этих атрибутов будут обязательными, а для некоторых других атрибутов будут назначены значения по умолчанию. В следующей таблице перечисляются типы данных и значения по умолчанию, которые формирует сервер, если значения предоставлены не были.

Тип данных	localeID*	sqlCompareOptions	sqlCollationVersion	SqlSortId	Максимальная длина	Точность	Масштаб
char, varchar, binary	-1	'Default'	0	0	8000	Неприменимо**	Неприменимо
nvarchar	-1	'Default'	0	0	4000	Неприменимо	Неприменимо
decimal, float, real	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	18	0
integer, bigint, tinyint, smallint	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо
datetime2	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	7
datetime offset	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	7
datetime, date, smalldatetime	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо
money, smallmoney	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо
time	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	Неприменимо	7

* значение localeID, равное -1, означает языковой стандарт по умолчанию. Языковой стандарт английского языка — 1033.

** Неприменимо — во время операции выбора из набора столбцов нет никаких выходных значений для этих атрибутов. Формируется ошибка, если в XML-представлении, предоставленном для набора столбцов в операции вставки или обновления, вызывающий указал значение для этого атрибута.

Безопасность

Модель безопасности набора столбцов работает схожим образом с моделью безопасности между таблицами и столбцами. Наборы столбцов могут быть визуализированы как минитаблица; операции выбора для данной минитаблицы имеют вид SELECT *. Однако связь между набором столбцов и разреженными столбцами — это связь группирования, а не просто контейнер. Модель безопасности проверяет безопасность столбцов в наборе столбцов и выполняет операции DENY над базовыми разреженными столбцами. Далее приводятся дополнительные характеристики модели безопасности.

• Права доступа могут быть предоставлены и отменены на столбец в наборе столбцов так же, как и на любой другой столбец в таблице.

• Выполнение инструкции GRANT или REVOKE для разрешений SELECT, INSERT, UPDATE, DELETE и REFERENCES для столбца в наборе столбцов не распространяется на базовые столбцы-участники этого набора. Оно применяется только к столбцу в наборе столбцов. Разрешение DENY для набора столбцов распространяется на базовые разреженные столбцы таблицы.

• Чтобы выполнять инструкции SELECT, INSERT, UPDATE и DELETE над столбцами в наборе столбцов, пользователь должен иметь необходимые разрешения на столбец набора столбцов, а также соответствующее разрешение на все разреженные столбцы в таблице. Поскольку набор столбцов представляет все разреженные столбцы в таблице, пользователь должен обладать разрешением на все разреженные столбцы, включая и те, которые не будут изменены.

• Выполнение инструкции REVOKE над разреженным столбцом или набором столбцов устанавливает для него параметры безопасности, заданные по умолчанию для его родительского объекта.

Примеры

В следующих примерах в таблице документа содержится обычный набор столбцов DocID и Title. Производственной группе необходимы столбцы ProductionSpecification и ProductionLocation для всех рабочих документов. Группе сбыта необходим столбец MarketingSurveyGroup для документов сбыта.

А. Создание таблицы с набором столбцов

В следующем примере создается таблица, в которой используются разреженные столбцы и содержится набор столбцов SpecialPurposeColumns. В этом примере в таблицу вставляются две строки, а затем из таблицы выбираются данные.

Примечание
Эта таблица насчитывает лишь пять столбцов, что упрощает ее отображение и чтение.

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

Б. Вставка данных в таблицу с использованием имен разреженных столбцов

В следующих примерах в таблицу, созданную в примере А, вставляются две строки. В примерах используются имена разреженных столбцов; набор столбцов не упоминается.

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

В. Вставка данных в таблицу с использованием имени набора столбцов

В следующем примере в таблицу, созданную в примере А, вставляется третья строка. В этот раз имена разреженных столбцов не используются. Вместо этого используется имя набора столбцов, а операция вставки предоставляет значения для двух из четырех разреженных столбцов в формате XML.

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

Г. Рассмотрение результатов для набора столбцов при выполнении инструкции SELECT *

В следующем примере из таблицы, содержащей набор столбцов, выбираются все столбцы. Возвращается XML-столбец, содержащий сочетание значений разреженных столбцов. Разреженные столбцы не возвращаются индивидуально.

SELECT * FROM DocumentStoreWithColumnSet ;

Ниже приводится результирующий набор.

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>

Д. Рассмотрение результатов выбора набора столбцов с использованием его имени

Поскольку производственному отделу не нужны маркетинговые данные, в этом примере для ограничения выходных данных добавляется предложение WHERE. В этом примере используется имя набора столбцов.

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

Ниже приводится результирующий набор.

DocID Title SpecialPurposeColumns

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

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

Е. Рассмотрение результатов выбора разреженных столбцов с использованием их имен

Несмотря на то, что таблица содержит набор столбцов, можно выполнять запросы из таблицы с использованием имен отдельных столбцов. Это показано в следующем примере.

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

Ниже приводится результирующий набор.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

3 Tire Spec 2 AXW9R411 38

Ж. Обновление таблицы с помощью набора столбцов

В следующем примере третья запись обновляется новыми значениями для обоих разреженных столбцов, использующихся в этой строке.

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

Важно!
Инструкция UPDATE, использующая набор столбцов, обновляет все разреженные столбцы в таблице. Для всех разреженных столбцов, которые не были упомянуты, устанавливается значение NULL.

В следующем примере обновляется третья запись, однако значение указывается только для одного из двух заполненных столбцов. Второй столбец, ProductionLocation, не включен в инструкцию UPDATE, и для него устанавливается значение NULL.

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

См. также

Основные понятия

Использование разреженных столбцов