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

ОБЛАСТЬ ПРИМЕНЕНИЯ ЭТОЙ СТАТЬИ: даSQL Server (начиная с 2016)даБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Разреженные столбцы — это обычные столбцы, имеющие оптимизированное хранилище для значений NULL.Sparse columns are ordinary columns that have an optimized storage for null values. Разреженные столбцы уменьшают пространство, необходимое для хранения значений NULL, однако увеличивается стоимость получения значений, отличных от NULL.Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Разреженные столбцы следует использовать только в том случае, если экономится не менее чем от 20 до 40 процентов места.Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Наборы столбцов и разреженные столбцы определяются с помощью инструкций CREATE TABLE и ALTER TABLE .Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Разреженные столбцы можно использовать совместно с наборами столбцов и фильтруемыми индексами.Sparse columns can be used with column sets and filtered indexes:

  • Наборы столбцовColumn sets

    Инструкции INSERT, UPDATE и DELETE могут ссылаться на разреженные столбцы по именам.INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. Также можно просматривать и работать со всеми разреженными столбцами таблицы, объединенными в один XML-столбец.However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. Такой столбец называется набором столбцов.This column is called a column set. Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.For more information about column sets, see Use Column Sets.

  • Отфильтрованные индексыFiltered indexes

    Поскольку разреженные столбцы имеют много строк со значениями NULL, они особенно подходят для фильтруемых индексов.Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. Фильтруемый индекс на основе разреженного столбца может индексировать только те строки, значения которых заполнены.A filtered index on a sparse column can index only the rows that have populated values. Таким образом создается более компактный и эффективный индекс.This creates a smaller and more efficient index. Дополнительные сведения см. в разделе Create Filtered Indexes.For more information, see Create Filtered Indexes.

    Разреженные столбцы и фильтруемые индексы позволяют приложениям, таким как Windows SharePoint ServicesWindows SharePoint Services, эффективно хранить и получать доступ к большому числу определяемых пользователем свойств с помощью SQL Server 2017SQL Server 2017.Sparse columns and filtered indexes enable applications, such as Windows SharePoint ServicesWindows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2017SQL Server 2017.

Свойства разреженных столбцовProperties of Sparse Columns

Разреженные столбцы имеют следующие характеристики.Sparse columns have the following characteristics:

  • Компонент Компонент SQL Server Database EngineSQL Server Database Engine использует ключевое слово SPARSE в определении столбца, чтобы оптимизировать хранение значений в этом столбце.The Компонент SQL Server Database EngineSQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Следовательно, если в любой строке таблицы этот столбец содержит значение NULL, места для хранения этих значений не требуется.Therefore, when the column value is NULL for any row in the table, the values require no storage.

  • Представления каталога таблицы, имеющей разреженные столбцы, идентичны представлениям обычной таблицы.Catalog views for a table that has sparse columns are the same as for a typical table. Представление каталога sys.columns содержит по строке для каждого столбца в таблице, включая набор столбцов, если он был определен.The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.

  • Разреженные столбцы являются свойством слоя хранилища, а не логической таблицы.Sparse columns are a property of the storage layer, rather than the logical table. Поэтому инструкция SELECT…INTO не копирует свойство разреженного столбца в новую таблицу.Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

  • Функция COLUMNS_UPDATED возвращает значение типа varbinary , показывающее все столбцы, которые были обновлены в процессе DML-действия.The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. Функция COLUMNS_UPDATED возвращает следующие биты.The bits that are returned by the COLUMNS_UPDATED function are as follows:

    • Если разреженный столбец был явно обновлен, соответствующий бит, представляющий этот столбец, и бит, представляющий набор столбцов, устанавливаются в 1.When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.

    • Если набор столбцов был явно обновлен, бит, представляющий набор столбцов, и биты, представляющие все разреженные столбцы в таблице, устанавливаются в 1.When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.

    • При операциях вставки всем битам присваивается значение 1.For insert operations, all bits are set to 1.

      Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.For more information about columns sets, see Use Column Sets.

    Столбцы следующих типов данных не могут быть указаны как SPARSE.The following data types cannot be specified as SPARSE:

geographygeography texttext
geometrygeometry timestamptimestamp
imageimage определяемые пользователем типы данныхuser-defined data types
ntextntext

Предполагаемая экономия места по типам данныхEstimated Space Savings by Data Type

Для хранения значений, отличных от NULL, в разреженных столбцах требуется больше места, чем для хранения идентичных данных, но не отмеченных, как SPARSE.Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. В следующих таблицах показано использование пространства для каждого типа данных.The following tables show the space usage for each data type. Столбец Процент значений NULL показывает, какой процент данных должен содержать значения NULL для достижения общей экономии пространства в 40 процентов.The NULL Percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.

Типы данных фиксированной длиныFixed-Length Data Types

Тип данныхData type Неразреженные байтыNonsparse bytes Разреженные байтыSparse bytes Процент значений NULLNULL percentage
bitbit 0.1250.125 55 98%98%
tinyinttinyint 11 55 86%86%
smallintsmallint 22 66 76%76%
intint 44 88 64%64%
bigintbigint 88 1212 52%52%
realreal 44 88 64%64%
floatfloat 88 1212 52%52%
smallmoneysmallmoney 44 88 64%64%
moneymoney 88 1212 52%52%
smalldatetimesmalldatetime 44 88 64%64%
datetimedatetime 88 1212 52%52%
uniqueidentifieruniqueidentifier 1616 2020 43%43%
datedate 33 77 69%69%

Типы данных с длиной, зависящей от точностиPrecision-Dependent–Length Data Types

Тип данныхData type Неразреженные байтыNonsparse bytes Разреженные байтыSparse bytes Процент значений NULLNULL percentage
datetime2(0)datetime2(0) 66 1010 57%57%
datetime2(7)datetime2(7) 88 1212 52%52%
time(0)time(0) 33 77 69%69%
time(7)time(7) 55 99 60%60%
datetimetoffset(0)datetimetoffset(0) 88 1212 52%52%
datetimetoffset (7)datetimetoffset (7) 1010 1414 49%49%
decimal/numeric(1,s)decimal/numeric(1,s) 55 99 60%60%
decimal/numeric(38,s)decimal/numeric(38,s) 1717 2121 42%42%
vardecimal(p,s)vardecimal(p,s) Используйте тип decimal в качестве консервативной оценки.Use the decimal type as a conservative estimate.

Типы данных с длиной, зависящей от данныхData-Dependent–Length Data Types

Тип данныхData type Неразреженные байтыNonsparse bytes Разреженные байтыSparse bytes Процент значений NULLNULL percentage
sql_variantsql_variant Зависит от базового типа данныхVaries with the underlying data type
varchar или charvarchar or char 2*2* 4*4* 60%60%
nvarchar или ncharnvarchar or nchar 2*2* 4*+4*+ 60%60%
varbinary или binaryvarbinary or binary 2*2* 4*4* 60%60%
xmlxml 2*2* 4*4* 60%60%
hierarchyidhierarchyid 2*2* 4*4* 60%60%

*Длина равна средней длине данных, содержащихся в типе, плюс 2 или 4 байта.*The length is equal to the average of the data that is contained in the type, plus 2 or 4 bytes.

Расход памяти при обновлении разреженных столбцовIn-Memory Overhead Required for Updates to Sparse Columns

При проектировании таблиц с разреженными столбцами учитывайте, что при выполнении операции обновления строки требуется 2 дополнительных байта на каждый разреженный столбец, не имеющий значения NULL, в таблице.When designing tables with sparse columns, keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated. Из-за этого операция обновления может непредвиденно завершиться ошибкой 576 в том случае, если общий размер строки, включая дополнительную память, превысит 8019 байт и будут отсутствовать столбцы, которые можно будет разместить вне строки.As a result of this additional memory requirement, updates can fail unexpectedly with error 576 when the total row size, including this memory overhead, exceeds 8019, and no columns can be pushed off the row.

Рассмотрим пример таблицы, которая содержит 600 разреженных столбцов типа bigint.Consider the example of a table that has 600 sparse columns of type bigint. Если 571 столбец имеет значения, отличные от NULL, общий размер на диске составит 571 * 12 = 6852 байта.If there are 571 non-null columns, then the total size on disk is 571 * 12 = 6852 bytes. После добавления дополнительных ресурсов строки и заголовка разреженного столбца размер увеличится до 6895 байт.After including additional row overhead and the sparse column header, this increases to around 6895 bytes. Для страницы по-прежнему доступно на диске около 1124 байта.The page still has around 1124 bytes available on disk. При этом создается впечатление, что дополнительные столбцы могут быть успешно обновлены.This can give the impression that additional columns can be updated successfully. Но во время обновления появляется дополнительный расход памяти, который составляет 2*(число разреженных столбцов со значением, отличным от NULL).However, during the update, there is additional overhead in memory which is 2*(number of non-null sparse columns). В этом примере размер строки на диске увеличится приблизительно до 8037 байт, включая дополнительный расход 2 * 571 = 1142 байта.In this example, including the additional overhead – 2 * 571 = 1142 bytes – increases the row size on disk to around 8037 bytes. Это значение превышает максимально допустимый размер 8019 байт.This size exceeds the maximum allowed size of 8019 bytes. Так как все столбцы содержат тип данных фиксированной длины, они не могут быть размещены вне строки.Since all the columns are fixed-length data types, they cannot be pushed off the row. В результате этого операция обновления завершится ошибкой 576.As a result, the update fails with the 576 error.

Ограничения на использование разреженных столбцовRestrictions for Using Sparse Columns

Разреженные столбцы могут иметь любой тип данных SQL ServerSQL Server . Они работают так же, как и другие столбцы, но со следующими ограничениями.Sparse columns can be of any SQL ServerSQL Server data type and behave like any other column with the following restrictions:

  • Разреженный столбец должен допускать значения NULL и не может иметь свойств ROWGUIDCOL или IDENTITY.A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties. Разреженный столбец не может иметь следующие типы данных: text, ntext, image, timestamp, определяемый пользователем тип данных, geometryили geography; также он не может иметь атрибут FILESTREAM.A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.

  • Разреженный столбец не может иметь значения по умолчанию.A sparse column cannot have a default value.

  • Разреженный столбец не может быть привязан к правилу.A sparse column cannot be bound to a rule.

  • Хотя вычисляемый столбец и может содержать разреженный столбец, но сам вычисляемый столбец не может быть отмечен как SPARSE.Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.

  • Маску данных можно определить только для разреженного столбца, не входящего в набор столбцов.A data mask can be defined on a sparse column, but not on a sparse column that is part of a column set.

  • Разреженный столбец не может быть частью кластеризованного индекса или индекса уникального первичного ключа.A sparse column cannot be part of a clustered index or a unique primary key index. Однако материализованные и нематериализованные вычисляемые столбцы, определенные для разреженных столбцов, могут быть частью кластеризованного ключа.However, both persisted and nonpersisted computed columns that are defined on sparse columns can be part of a clustered key.

  • Разреженный столбец не может быть использован в качестве ключа секции для кластеризованного индекса или кучи.A sparse column cannot be used as a partition key of a clustered index or heap. Однако разреженный столбец может быть использован в качестве ключа секции для некластеризованного индекса.However, a sparse column can be used as the partition key of a nonclustered index.

  • Разреженный столбец не может быть частью определяемого пользователем табличного типа, используемого в переменных таблицы и в возвращающих табличное значение параметрах.A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.

  • Разреженные столбцы несовместимы со сжатием данных.Sparse columns are incompatible with data compression. Поэтому разреженные столбцы нельзя добавить в сжатые таблицы, а таблицы с разреженными столбцами нельзя сжать.Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.

  • Преобразование столбца из разреженного в неразреженный (или наоборот) требует изменения формата хранения столбца.Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column. Компонент SQL Server Database Engine для выполнения данного изменения использует следующую процедуру.The SQL Server Database Engine uses the following procedure to accomplish this change:

    1. В таблицу добавляется новый столбец с новым размером хранения и форматом.Adds a new column to the table in the new storage size and format.

    2. Для каждой строки в таблице производится обновление и копирование значений, хранимых в старом столбце, в новый столбец.For each row in the table, updates and copies the value stored in the old column to the new column.

    3. Из схемы таблицы удаляется старый столбец.Removes the old column from the table schema.

    4. Перестраивает таблицу (если нет кластеризованного индекса) или перестраивает кластеризованный индекс для освобождения места, используемого старым столбцом.Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

    Примечание

    Шаг 2 может завершиться неудачно, если размер данных в строке превышает максимально допустимый размер строки.Step 2 can fail when the size of the data in the row exceeds the maximum allowable row size. Этот размер включает размер данных, хранимых в старом столбце, и обновленных данных, хранимых в новом столбце.This size includes the size of the data stored in the old column and the updated data stored in the new column. Данное ограничение составляет 8 060 байт для таблиц, не содержащих разреженные столбцы, и 8 018 байт для таблиц, содержащих их.This limit is 8060 bytes for tables that do not contain any sparse columns or 8018 bytes for tables that contain sparse columns. Данная ошибка может возникнуть, даже если все подходящие столбцы включают внестрочные данные.This error can occur even if all eligible columns have been pushed off-row.

  • При преобразовании неразреженного столбца в разреженный увеличится место, необходимое для хранения значений, отличных от NULL.When you change a non-sparse column to a sparse column, the sparse column will consume more space for non-null values. Если строка имеет длину, близкую к максимальной, операция может завершиться неудачно.When a row is close to the maximum row size limit, the operation can fail.

Технологии SQL Server, поддерживающие разреженные столбцыSQL Server Technologies That Support Sparse Columns

В этом разделе описывается поддержка разреженных столбцов в следующих технологиях SQL ServerSQL Server .This section describes how sparse columns are supported in the following SQL ServerSQL Server technologies:

  • Репликация транзакцийTransactional replication

    Репликация транзакций поддерживает разреженные столбцы, однако не поддерживает наборы столбцов, которые могут быть использованы с разреженными столбцами.Transactional replication supports sparse columns, but it does not support column sets, which can be used with sparse columns. Дополнительные сведения о наборах столбцов см. в разделе Использование наборов столбцов.For more information about column sets, see Use Column Sets.

    Репликация атрибута SPARSE определяется параметром схемы, задаваемым с помощью процедуры sp_addarticle либо с помощью диалогового окна Свойства статьи в среде Среда SQL Server Management StudioSQL Server Management Studio.The replication of the SPARSE attribute is determined by a schema option that is specified by using sp_addarticle or by using the Article Properties dialog box in Среда SQL Server Management StudioSQL Server Management Studio. Более ранние версии SQL ServerSQL Server не поддерживают разреженные столбцы.Earlier versions of SQL ServerSQL Server do not support sparse columns. Если необходимо реплицировать данные в более раннюю версию, следует указать, что атрибут SPARSE не подлежит репликации.If you must replicate data to an earlier version, specify that the SPARSE attribute should not be replicated.

    В опубликованные таблицы нельзя добавлять новые разреженные столбцы или изменять свойство SPARSE существующих столбцов.For tables that are published, you cannot add any new sparse columns to a table or change the sparse property of an existing column. Если необходимо выполнить подобную операцию, следует удалить и повторно создать публикацию.If such an operation is required, drop and re-create the publication.

  • Репликация слияниемMerge replication

    Репликация слиянием не поддерживает разреженные столбцы и наборы столбцов.Merge replication does not support sparse columns or column sets.

  • отслеживание измененийChange tracking

    Отслеживание изменений поддерживает разреженные столбцы и наборы столбцов.Change tracking supports sparse columns and column sets. Если в таблице обновляется набор столбцов, система отслеживания изменений считает это обновлением целой строки.When a column set is updated in a table, change tracking treats this as an update to the whole row. Более подробное отслеживание изменений для определения точного набора разреженных столбцов, который был изменен в ходе операции обновления набора столбцов, не осуществляется.No detailed change tracking is provided to obtain the exact set of sparse columns that are updated through the column set update operation. Если разреженные столбцы обновляются явно с помощью инструкции DML, система отслеживания изменений обрабатывает их обычным образом и можно идентифицировать точный набор измененных столбцов.If the sparse columns are updated explicitly through a DML statement, change tracking on them will work ordinarily and can identify the exact set of changed columns.

  • система отслеживания измененных данныхChange data capture

    Система отслеживания измененных данных поддерживает разреженные столбцы, но не поддерживает наборы столбцов.Change data capture supports sparse columns, but it does not support column sets.

  • При копировании таблицы свойство разреженности столбца не сохраняется.The sparse property of a column is not preserved when the table is copied.

ПримерыExamples

В данном примере таблица документа содержит обычный набор со столбцами DocID и Title.In this example, a document table contains a common set that has the columns DocID and Title. Производственной группе необходимы столбцы ProductionSpecification и ProductionLocation для всех рабочих документов.The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. Группе сбыта необходим столбец MarketingSurveyGroup для документов сбыта.The Marketing group wants a MarketingSurveyGroup column for marketing documents. Код из этого примера создает таблицу, использующую разреженные столбцы, вставляет в таблицу две строки, затем выбирает из таблицы данные.The code in this example creates a table that uses sparse columns, inserts two rows into the table, and then selects data from the table.

Примечание

Эта таблица насчитывает лишь пять столбцов, что упрощает ее отображение и чтение.This table has only five columns to make it easier to display and read. При установленном параметре ANSI_NULL_DFLT_ON объявлять разреженные столбцы допускающими значения NULL необязательно.Declaring the sparse columns to be nullable is optional if the ANSI_NULL_DFLT_ON option is set.

USE AdventureWorks2012;  
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  

При выборе всех столбцов таблицы возвращается обычный результирующий набор.To select all the columns from the table returns an ordinary result set.

SELECT * FROM DocumentStore ;  

Ниже приводится результирующий набор.Here is the result set.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Поскольку производственному отделу не нужны маркетинговые данные, должен быть предоставлен список столбцов, содержащих только необходимые данные, как это показано в следующем запросе.Because the Production department is not interested in the marketing data, they want to use a column list that returns only columns of interest, as shown in the following query.

SELECT DocID, Title, ProductionSpecification, ProductionLocation   
FROM DocumentStore   
WHERE ProductionSpecification IS NOT NULL ;  

Ниже приводится результирующий набор.Here is the result set.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

См. также:See Also

Использование наборов столбцов Use Column Sets
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
sys.columns (Transact-SQL)sys.columns (Transact-SQL)