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

Статья
07/05/2024

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

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

Управлять данными в разреженных столбцах можно с помощью имен индивидуальных столбцов либо ссылаясь на имя набора столбцов и указывая значения набора столбцов, используя 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	Максимальная длина	Точность	Масштабировать
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 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

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

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

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

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

Внимание

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

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

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

Следующие шаги

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

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

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

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

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

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

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

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

Примеры

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

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

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

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

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

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

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

Следующие шаги

Обратная связь

Дополнительные ресурсы