Поделиться через

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

Относится к: SQL Server 2016 (13.x) и более поздние версии Azure SQL DatabaseAzure SQL Managed InstanceSQL Database в Microsoft Fabric

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

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

Определить набор столбцов можно с помощью ключевых слов *<column_set_name>* FOR ALL_SPARSE_COLUMNS в инструкциях CREATE TABLE и ALTER TABLE.

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Набор столбцов не может быть частью никакого вида индексов. Это касается 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-представление набора столбцов.

Warning

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

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

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

При вставке или обновлении значений разреженных столбцов с помощью набора XML-столбцов производится неявное преобразование значений, вставляемых в лежащие в основе разреженные столбцы, из типа данных xml . В случае большинства числовых типов данных, включая bigint, int, smallint, tinyint, bit, float и real, пустое значение в 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 Максимальная длина Precision Scale
char, varchar, binary -1 'Default' 0 0 8000 Неприменимо** Неприменимо
nvarchar -1 'Default' 0 0 4000 Неприменимо Неприменимо
десятичная, плавающая, реальная Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо 18 0
целое число, bigint, tinyint, smallint Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо
datetime2 Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо 7
смещение даты и времени Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо 7
datetime, date, smalldatetime Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо
деньги, маленькие деньги Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо
time Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо Неприменимо 7

* localeID -1 означает локаль по умолчанию. Локаль английского языка имеет идентификатор 1033.

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

Security

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

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

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

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

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

Examples

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

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

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

Note

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

USE AdventureWorks2022;  
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. Вставка данных в таблицу с помощью имен разреженных столбцов

В следующих примерах вставьте две строки в таблицу, созданную в примере 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

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

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

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

D. Просмотрите результаты набора столбцов при использовании 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>

E. Просмотрите результаты выбора столбца по имени

Поскольку производственному отделу не нужны маркетинговые данные, в этом примере для ограничения выходных данных добавляется предложение 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

Important

Инструкция UPDATE, использующая набор столбцов, обновляет все разреженные столбцы в таблице. Разреженные столбцы, на которые не ссылается ссылка, обновляются на NULL.

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

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

Дальнейшие шаги

  • Last updated on