Использование разреженных столбцов
Применимо к:
SQL Server 2016 (13.x) и более поздних версий Azure SQL Database Управляемый экземпляр SQL Azure
Разреженные столбцы — это обычные столбцы, имеющие оптимизированное хранилище для значений NULL. Разреженные столбцы уменьшают пространство, необходимое для хранения значений NULL, но увеличивают затраты на получение значений, отличных от NULL. Разреженные столбцы следует использовать только в том случае, если экономится не менее чем от 20 до 40 процентов места. Наборы столбцов и разреженные столбцы определяются с помощью инструкций CREATE TABLE и ALTER TABLE .
Разреженные столбцы можно использовать совместно с наборами столбцов и фильтруемыми индексами.
Наборы столбцов
Инструкции INSERT, UPDATE и DELETE могут ссылаться на разреженные столбцы по именам. Также можно просматривать и работать со всеми разреженными столбцами таблицы, объединенными в один XML-столбец. Такой столбец называется набором столбцов. Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.
Отфильтрованные индексы
Поскольку разреженные столбцы имеют много строк со значениями NULL, они особенно подходят для фильтруемых индексов. Фильтруемый индекс на основе разреженного столбца может индексировать только те строки, значения которых заполнены. Таким образом создается более компактный и эффективный индекс. Дополнительные сведения см. в разделе Create Filtered Indexes.
Разреженные столбцы и отфильтрованные индексы позволяют приложениям, таким как Windows SharePoint Services, эффективно хранить и получать доступ к большому количеству определяемых пользователем свойств с помощью SQL Server.
Свойства разреженных столбцов
Разреженные столбцы имеют следующие характеристики.
Ядро СУБД SQL Server использует ключевое слово SPARSE в определении столбца для оптимизации хранения значений в этом столбце. Следовательно, если в любой строке таблицы этот столбец содержит значение NULL, места для хранения этих значений не требуется.
Представления каталога таблицы, имеющей разреженные столбцы, идентичны представлениям обычной таблицы. Представление каталога
sys.columnsсодержит по строке для каждого столбца в таблице, включая набор столбцов, если он был определен.Разреженные столбцы являются свойством слоя хранилища, а не логической таблицы. Поэтому инструкция
SELECT ... INTOне копирует свойство разреженного столбца в новую таблицу.Функция COLUMNS_UPDATED возвращает значение типа varbinary , показывающее все столбцы, которые были обновлены в процессе DML-действия. Функция COLUMNS_UPDATED возвращает следующие биты.
Если разреженный столбец был явно обновлен, соответствующий бит, представляющий этот столбец, и бит, представляющий набор столбцов, устанавливаются в 1.
Если набор столбцов был явно обновлен, бит, представляющий набор столбцов, и биты, представляющие все разреженные столбцы в таблице, устанавливаются в 1.
При операциях вставки всем битам присваивается значение 1.
Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.
Столбцы следующих типов данных не могут быть указаны как SPARSE.
geography
geometry
image
ntext
text
timestamp
определяемые пользователем типы данных
Предполагаемая экономия места по типам данных
Для хранения значений, отличных от NULL, в разреженных столбцах требуется больше места, чем для хранения идентичных данных, но не отмеченных, как SPARSE. В следующих таблицах показано использование пространства для каждого типа данных. Столбец Процент значений NULL показывает, какой процент данных должен содержать значения NULL для достижения общей экономии пространства в 40 процентов.
Типы данных фиксированной длины
| Тип данных | Неразреженные байты | Разреженные байты | Процент значений NULL |
|---|---|---|---|
| bit | 0.125 | 5 | 98% |
| tinyint | 1 | 5 | 86% |
| smallint | 2 | 6 | 76% |
| int | 4 | 8 | 64% |
| bigint | 8 | 12 | 52% |
| real | 4 | 8 | 64% |
| float | 8 | 12 | 52% |
| smallmoney | 4 | 8 | 64% |
| money | 8 | 12 | 52% |
| smalldatetime | 4 | 8 | 64% |
| datetime | 8 | 12 | 52% |
| uniqueidentifier | 16 | 20 | 43% |
| date | 3 | 7 | 69% |
Типы данных с длиной, зависящей от точности
| Тип данных | Неразреженные байты | Разреженные байты | Процент значений NULL |
|---|---|---|---|
| datetime2(0) | 6 | 10 | 57% |
| datetime2(7) | 8 | 12 | 52% |
| time(0) | 3 | 7 | 69% |
| time(7) | 5 | 9 | 60 % |
| datetimetoffset(0) | 8 | 12 | 52% |
| datetimetoffset (7) | 10 | 14 | 49% |
| decimal/numeric(1,s) | 5 | 9 | 60 % |
| decimal/numeric(38,s) | 17 | 21 | 42% |
| vardecimal(p,s) | Используйте тип decimal в качестве консервативной оценки. |
Типы данных с длиной, зависящей от данных
| Тип данных | Неразреженные байты | Разреженные байты | Процент значений NULL |
|---|---|---|---|
| sql_variant | Зависит от базового типа данных | ||
| varchar или char | 2* | 4* | 60 % |
| nvarchar или nchar | 2* | 4*+ | 60 % |
| varbinary или binary | 2* | 4* | 60 % |
| xml | 2* | 4* | 60 % |
| hierarchyid | 2* | 4* | 60 % |
*Длина равна средней длине данных, содержащихся в типе, плюс 2 или 4 байта.
Расход памяти при обновлении разреженных столбцов
При проектировании таблиц с разреженными столбцами учитывайте, что при выполнении операции обновления строки требуется 2 дополнительных байта на каждый разреженный столбец, не имеющий значения NULL, в таблице. Из-за этого операция обновления может непредвиденно завершиться ошибкой 576 в том случае, если общий размер строки, включая дополнительную память, превысит 8019 байт и будут отсутствовать столбцы, которые можно будет разместить вне строки.
Рассмотрим пример таблицы, которая содержит 600 разреженных столбцов типа bigint. Если 571 столбец имеет значения, отличные от NULL, общий размер на диске составит 571 * 12 = 6852 байта. После добавления дополнительных ресурсов строки и заголовка разреженного столбца размер увеличится до 6895 байт. Для страницы по-прежнему доступно на диске около 1124 байта. При этом создается впечатление, что дополнительные столбцы могут быть успешно обновлены. Но во время обновления появляется дополнительный расход памяти, который составляет 2 * (число разреженных столбцов со значением, отличным от NULL). В этом примере, включая дополнительные издержки ( 2 * 571 = 1142 байта), увеличивает размер строки на диске примерно до 8037 байт. Это значение превышает максимально допустимый размер 8019 байт. Так как все столбцы содержат тип данных фиксированной длины, они не могут быть размещены вне строки. В результате этого операция обновления завершится ошибкой 576.
Ограничения на использование разреженных столбцов
Разреженные столбцы могут иметь любой тип данных SQL Server и работать так же, как любой другой столбец со следующими ограничениями:
Разреженный столбец должен допускать значения NULL и не может иметь свойств ROWGUIDCOL или IDENTITY. Разреженный столбец не может иметь следующие типы данных: text, ntext, image, timestamp, определяемый пользователем тип данных, geometryили geography; также он не может иметь атрибут FILESTREAM.
Разреженный столбец не может иметь значения по умолчанию.
Разреженный столбец не может быть привязан к правилу.
Хотя вычисляемый столбец и может содержать разреженный столбец, но сам вычисляемый столбец не может быть отмечен как SPARSE.
Маску данных можно определить только для разреженного столбца, не входящего в набор столбцов.
Разреженный столбец не может быть частью кластеризованного индекса или индекса уникального первичного ключа. Однако материализованные и нематериализованные вычисляемые столбцы, определенные для разреженных столбцов, могут быть частью кластеризованного ключа.
Разреженный столбец не может быть использован в качестве ключа секции для кластеризованного индекса или кучи. Однако разреженный столбец может быть использован в качестве ключа секции для некластеризованного индекса.
Разреженный столбец не может быть частью определяемого пользователем табличного типа, используемого в переменных таблицы и в возвращающих табличное значение параметрах.
Разреженные столбцы несовместимы со сжатием данных. Поэтому разреженные столбцы нельзя добавить в сжатые таблицы, а таблицы с разреженными столбцами нельзя сжать.
Преобразование столбца из разреженного в неразреженный (или наоборот) требует изменения формата хранения столбца. Компонент SQL Server Database Engine для выполнения данного изменения использует следующую процедуру.
В таблицу добавляется новый столбец с новым размером хранения и форматом.
Для каждой строки в таблице производится обновление и копирование значений, хранимых в старом столбце, в новый столбец.
Из схемы таблицы удаляется старый столбец.
Перестраивает таблицу (если нет кластеризованного индекса) или перестраивает кластеризованный индекс для освобождения места, используемого старым столбцом.
Примечание
Шаг 2 может завершиться неудачно, если размер данных в строке превышает максимально допустимый размер строки. Этот размер включает размер данных, хранимых в старом столбце, и обновленных данных, хранимых в новом столбце. Данное ограничение составляет 8 060 байт для таблиц, не содержащих разреженные столбцы, и 8 018 байт для таблиц, содержащих их. Данная ошибка может возникнуть, даже если все подходящие столбцы включают внестрочные данные.
При преобразовании неразреженного столбца в разреженный увеличится место, необходимое для хранения значений, отличных от NULL. Если строка имеет длину, близкую к максимальной, операция может завершиться неудачно.
Технологии SQL Server, поддерживающие разреженные столбцы
В этом разделе описывается поддержка разреженных столбцов в следующих SQL Server технологиях:
Репликация транзакций
Репликация транзакций поддерживает разреженные столбцы, однако не поддерживает наборы столбцов, которые могут быть использованы с разреженными столбцами. Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.
Репликация атрибута SPARSE определяется параметром схемы, заданным с помощью sp_addarticle или с помощью диалогового окна Свойства статьи в SQL Server Management Studio. Более ранние версии SQL Server не поддерживают разреженные столбцы. Если необходимо реплицировать данные в более раннюю версию, следует указать, что атрибут SPARSE не подлежит репликации.
В опубликованные таблицы нельзя добавлять новые разреженные столбцы или изменять свойство SPARSE существующих столбцов. Если необходимо выполнить подобную операцию, следует удалить и повторно создать публикацию.
Репликация слиянием
Репликация слиянием не поддерживает разреженные столбцы и наборы столбцов.
отслеживание изменений
Отслеживание изменений поддерживает разреженные столбцы и наборы столбцов. Если в таблице обновляется набор столбцов, система отслеживания изменений считает это обновлением целой строки. Более подробное отслеживание изменений для определения точного набора разреженных столбцов, который был изменен в ходе операции обновления набора столбцов, не осуществляется. Если разреженные столбцы обновляются явно с помощью инструкции DML, система отслеживания изменений обрабатывает их обычным образом и можно идентифицировать точный набор измененных столбцов.
система отслеживания измененных данных
Система отслеживания измененных данных поддерживает разреженные столбцы, но не поддерживает наборы столбцов.
При копировании таблицы свойство разреженности столбца не сохраняется.
Примеры
В данном примере таблица документа содержит обычный набор со столбцами DocID и Title. Производственной группе необходимы столбцы ProductionSpecification и ProductionLocation для всех рабочих документов. Группе сбыта необходим столбец MarketingSurveyGroup для документов сбыта. Код из этого примера создает таблицу, использующую разреженные столбцы, вставляет в таблицу две строки, затем выбирает из таблицы данные.
Примечание
Эта таблица насчитывает лишь пять столбцов, что упрощает ее отображение и чтение. При установленном параметре ANSI_NULL_DFLT_ON объявлять разреженные столбцы допускающими значения NULL необязательно. Если значение SET ANSI_DEFAULTS равно ON, включается SET ANSI_NULL_DFLT_ON. ANSI_DEFAULTS по умолчанию включено для большинства поставщиков соединений. Дополнительные сведения см. в разделе SET ANSI_DEFAULTS (Transact-SQL).
USE AdventureWorks2022;
GO
CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO
INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);
GO
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35');
GO
При выборе всех столбцов таблицы возвращается обычный результирующий набор.
SELECT * FROM DocumentStore ;
Результирующий набор:
DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup
1 Tire Spec 1 AXZZ217 27 NULL
2 Survey 2142 NULL NULL Men 25-35
Поскольку производственному отделу не нужны маркетинговые данные, должен быть предоставлен список столбцов, содержащих только необходимые данные, как это показано в следующем запросе.
SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStore
WHERE ProductionSpecification IS NOT NULL ;
Результирующий набор:
DocID Title ProductionSpecification ProductionLocation
1 Tire Spec 1 AXZZ217 27
См. также раздел
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по