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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• В редакторе таблиц 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-представление набора столбцов.

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

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

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

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 -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 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  

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

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

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 DocID, Title, SpecialPurposeColumns 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>

F. Наблюдение за результатами выбора разреженных столбцов по имени

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

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

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

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

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

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

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

См. также

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