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

Статья
06/16/2023

Применимо к: SQL Server 2016 (13.x) и более поздних версий Azure SQL Database Управляемый экземпляр 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 для документов сбыта.

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

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

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

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

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

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

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

Дальнейшие действия

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

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

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

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

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

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

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

Примеры

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

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

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

Г. Наблюдайте за результатами набора столбцов при использовании SELECT *

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

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

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

Дальнейшие действия

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

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

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