Хранение десятичных данных в виде значений переменной длины

Статья
12/15/2008

Добавления: 12 декабря 2006 г.

Типы данных decimal (десятичный) и numeric (числовой) обычно хранятся на диске как данные фиксированной длины. Тип данных numeric функционально эквивалентен типу данных decimal. В SQL Server 2005 с пакетом обновления 2 (SP2) и более поздних версиях типы данных decimal и numeric могут храниться в виде столбца переменной длины, если используется формат хранения vardecimal. Формат хранения vardecimal доступен только в выпусках SQL Server 2005 Enterprise Edition, Developer Edition и Evaluation Edition.

Примечание.
Vardecimal – это формат хранения, а не тип данных.

Использование формата хранения vardecimal может существенно уменьшить размер хранимых данных, но потребует немного дополнительного времени ЦП. Формат хранения vardecimal задается на уровне таблицы. Это означает, что нельзя хранить в формате vardecimal только часть столбцов типа decimal в таблице, а не все такие столбцы. Данные decimal сохраняют свой тип данных.

Если для таблицы задан формат хранения vardecimal, данные типа decimal хранятся на страницах данных, индекса и журнала в формате хранения vardecimal. Операция смены формата хранения выполняется в автономном режиме. Изменяемая таблица блокируется монопольно на все время операции и недоступна для параллельного чтения или записи.

Реализация формата хранения vardecimal

В зависимости от точности столбца (от 1 до 38) для хранения значения типа decimal требуется от 5 до 17 байт. Если формат хранения vardecimal не используется для таблицы, каждая запись в таблице занимает одно и то же число байт для каждого определенного десятичного столбца, даже если значением строки является 0, NULL или другое значение, которое может быть выражено меньшим числом байт, например число 3. Если таблица хранится в формате vardecimal, столбцы типа decimal каждой строки занимают лишь столько места, сколько требуется для хранения данного числа, плюс 2 дополнительных байта. Результат всегда находится в диапазоне от 5 до 20 байт. Этот размер включает 2 дополнительных байта для хранения смещения. Однако значение NULL и нули обрабатываются особо и занимают только 2 байта.

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

В таблице ниже показано число байт, необходимое для хранения десятичных данных в обычном фиксированном формате, и максимальное число байт для хранения десятичных данных в формате vardecimal. Если таблица хранится в фиксированном формате, указанные ниже значения используются для каждой строки. Если таблица хранится в формате vardecimal, для многих значений потребуется меньше байт, чем указано в таблице.

Точность столбца	Исходный фиксированный размер десятичного значения (байт)	Максимальный размер области данных vardecimal (байт)	Издержки на хранение смещения (байт)	Максимальный используемый размер хранения vardecimal (байт)
1-3	5	3	2	5
4-6	5	4	2	6
7-9	5	5	2	7
10-12	9	6	2	8
13-15	9	8	2	10
16-18	9	9	2	11
19	9	10	2	12
20-21	13	10	2	12
22-24	13	11	2	13
25-27	13	13	2	15
28	13	14	2	16
29-30	17	14	2	16
31-33	17	15	2	17
34-36	17	16	2	18
37-38	17	18	2	20

Использование формата хранения vardecimal

Формат хранения vardecimal можно использовать для решения следующих проблем.

Нехватка места на диске.
Доступ к диску (ввод-вывод) является узким местом и снижает производительность системы.
Для некоторых данных необходимо использовать высокий уровень точности, даже если многие из значений имеют небольшой размер, равны NULL или 0 (например, таблица в хранилище данных со столбцом типа decimal, в котором многие строки содержат 0 или целочисленные значения).

Смена формата хранения в таблице требует перестроения индексов таблицы (кластеризованного или некластеризованных). Если кластеризованный индекс таблицы содержит столбец типа decimal, все некластеризованные индексы также должны быть перестроены, поскольку содержат значения кластеризованного ключа. Если в кластеризованном индексе нет столбца типа decimal, некластеризованные индексы перестраиваются только в случае, если содержат столбец типа decimal. Если таблица является кучей (то есть не имеет кластеризованного индекса), все некластеризованные индексы должны быть перестроены, чтобы указывать на новые местоположения строк в куче.

Перестроение таблицы для включения или отключения формата хранения vardecimal может потребовать по меньшей мере в два раза больше места, чем исходная таблица. Если таблица не содержит столбцов типа decimal или numeric, включение формата хранения vardecimal является операцией только над метаданными. При перестроении таблицы и индексов выполняется множество операций с журналом.

Формат хранения vardecimal может использоваться как для таблиц, предназначенных только для чтения, так и для таблиц с возможностью чтения и записи. Экономию места для хранения в результате использования формата vardecimal следует соотносить с дополнительной нагрузкой на ЦП из-за преобразования формата хранения строк при каждом обращении к строкам. Кроме того, операции записи в таблицу, в которой используется формат хранения vardecimal, могут снизить производительность из-за большего числа разбиений страниц.

Ограничения формата хранения vardecimal

Существуют следующие ограничения.

Требуется SQL Server 2005 с пакетом обновления 2 (SP2) или более поздняя версия.
Использование формата хранения vardecimal не может быть включено в системных базах данных: master, model, tempdb, msdb и distribution. Если запрос сортирует данные, которые хранятся в формате vardecimal, эти данные сортируются в базе данных tempdb в фиксированном десятичном формате. Обычно для размещения этих данных в базе данных tempdb требуется значительно больше места, чем для исходной таблицы в формате vardecimal в исходной базе данных.
Формат хранения vardecimal не может применяться к представлениям, индексированным представлениям, XML-индексам и полнотекстовым индексам. Однако в таблицах, лежащих в основе этих объектов, формат хранения vardecimal может использоваться.
Для внутренних таблиц, например таблиц метаданных и уведомлений, формат хранения vardecimal не может использоваться.
Для функций, возвращающих табличное значение, формат хранения vardecimal не может использоваться.
Кроме того, столбец типа numeric, который хранится в формате vardecimal, не может быть зашифрован.
Гетерогенные секции (то есть секции в фиксированном десятичном формате и формате vardecimal) не поддерживаются.
Новые таблицы, создаваемые из таблицы с форматом хранения vardecimal с помощью синтаксиса Transact-SQL SELECT … INTO…, не наследуют формат хранения vardecimal.
Использование формата хранения vardecimal невозможно задать для баз данных с включенным зеркальным отображением. Чтобы включить формат хранения vardecimal для такой базы данных, необходимо сначала удалить ее зеркальное отображение. Но если формат хранения vardecimal включается или отключается только для отдельных таблиц, удаление зеркального отображения базы данных не требуется.

В SQL Server должна быть гарантия успешного выполнения всех обновлений и возможности возвращения таблицы в фиксированный десятичный формат. Поэтому таблица не может быть переведена в формат хранения vardecimal, если из-за дополнительных издержек размер существующей строки превысит 8060 байт или существующее значение индекса превысит 900 байт.

Примечание.
Формат хранения vardecimal отличается от хранения текстовых значений переменной длины (varchar) тем, что SQL Server позволяет создавать строки более 8060 байт, если все переменные столбцы имеют максимальный размер. SQL Server поддерживает ограничение в 8060 байт при вставке или обновлении текстовых данных. SQL Server не позволяет создать набор десятичных столбцов, размер которых может превысить 8060 байт на строку. Ограничение в 8060 байт поддерживается при смене формата таблицы на формат хранения vardecimal.

Формат хранения vardecimal отличается от хранения текстовых значений переменной длины (varchar) тем, что SQL Server позволяет создавать строки более 8060 байт, если все переменные столбцы имеют максимальный размер. SQL Server поддерживает ограничение в 8060 байт при вставке или обновлении текстовых данных. SQL Server не позволяет создать набор десятичных столбцов, размер которых может превысить 8060 байт на строку. Ограничение в 8060 байт поддерживается при смене формата таблицы на формат хранения vardecimal.

Если база данных передается методом отсоединения и присоединения в мастере копирования баз данных, операция присоединения закончится ошибкой, если целевой компонент Database Engine не является сервером SQL Server 2005 с пакетом обновления 2 (SP2) или более поздней версией. Если используется метод объектов SMO, то новая база данных и таблицы создаются без использования формата хранения vardecimal. После передачи база данных и таблицы могут быть переведены в формат vardecimal, если компонент Database Engine является сервером SQL Server 2005 с пакетом обновления 2 (SP2) или более поздней версией.

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

Резервное копирование и восстановление, зеркальное отображение базы данных, хранимая процедура sp_attach_db и доставка журналов правильно работают с форматом хранения vardecimal; однако для работы с базой данных, в которой используется формат vardecimal, каждый экземпляр SQL Server должен быть обновлен по меньшей мере до версии SQL Server 2005 с пакетом обновления 2 (SP2). Например, нельзя восстановить резервную копию журнала базы данных, в которой используется формат хранения vardecimal, в базе данных, где этот формат не включен; нельзя зеркально отображать базу данных, в которой используется формат vardecimal, в базе данных, где этот формат не включен; нельзя отсоединить базу данных, в которой используется формат vardecimal, в SQL Server 2005 с пакетом обновления 2 (SP2) и присоединить к более ранней версии SQL Server. Если полная резервная копия базы данных с включенным форматом хранения vardecimal восстанавливается в базе данных, где этот формат не включен, в восстановленной базе данных формат vardecimal будет включен.

Если формат хранения vardecimal включается для таблицы, цепочка резервных копий журналов остается действительной и база данных может быть восстановлена на основе последней полной резервной копии и действительной цепочки журналов. Чтобы не создавать недействительные резервные копии, необходимо перевести базу данных на простую модель восстановления, прежде чем отключать формат vardecimal в какой-либо таблице. После отключения формата vardecimal в любой таблице необходимо создать полную резервную копию базы данных.

Использование формата хранения vardecimal при зеркальном отображении базы данных

Использование формата хранения vardecimal при зеркальном отображении базы данных осуществляется следующим образом.

Использование формата vardecimal при зеркальном отображении базы данных

Обновите оба сервера, основной и зеркальный, по меньшей мере до версии SQL Server 2005 с пакетом обновления 2 (SP2).
Если в этот момент используется зеркальное отображение базы данных, отключите зеркальное отображение и удалите зеркальный сервер. Дополнительные сведения см. в разделе Как удалить зеркальное отображение базы данных (Transact-SQL).
Включите использование формата хранения vardecimal в основной базе данных и убедитесь в том, что для основной базы данных применяется полная модель восстановления.
Установите зеркальное отображение базы данных, используя для этого полную резервную копию и резервные копии журналов основной базы данных. Дополнительные сведения см. в разделе Как создать сеанс зеркального отображения базы данных с использованием проверки подлинности Windows (Transact-SQL).
Переведите отдельные таблицы на использование формата хранения vardecimal.

Примечание.
Для изменения формата хранения отдельных таблиц отключение зеркального отображения базы данных не требуется.

Отключение формата хранения vardecimal

Измените таблицы в основной базе данных, чтобы отключить формат хранения vardecimal.
Отключите зеркальное отображение базы данных.
Переведите основную базу данных на простую модель восстановления. Это приведет к разрыву цепочки журналов.
Отключите формат хранения vardecimal в основной базе данных.
Удалите базу данных на зеркальном сервере.
Верните основную базу данных к полной модели восстановления.
Создайте резервную копию основной базы данных и заново установите зеркальное отображение базы данных.

Влияние формата хранения vardecimal на операции репликации

Для таблиц, в которых используется формат хранения vardecimal, репликация осуществляется обычным способом с учетом следующих особенностей.

Типы данных decimal, которые хранятся в формате vardecimal, преобразуются в фиксированный десятичный формат для передачи во время репликации. В базе данных распространителя (distribution) включить формат хранения vardecimal невозможно. Поэтому данные, сохраняемые в таблицах репликации в базе данных распространителя, не хранятся в формате vardecimal. У подписчика записи журнала используются обычным способом.
Таблицу с форматом хранения vardecimal можно реплицировать в таблицу фиксированного десятичного формата, а таблицу фиксированного десятичного формата — в таблицу с форматом хранения vardecimal.
Процесс создания таблиц, связанный с новой подпиской, не создает таблиц с форматом хранения vardecimal. Это позволяет успешно выполнить репликацию независимо от используемого пакета обновления компонента Database Engine или включения формата хранения vardecimal в базе данных подписчика. Для таблицы подписчика использование формата хранения vardecimal может быть включено на подписчике после создания таблицы или путем изменения сценариев создания, прежде чем они применяются.

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

Подписчик	Сценарий
SQL Server 2000 или SQL Server версии 7.0	Сценарии создания таблицы могут использоваться без изменения.
SQL Server 2005, в базе данных использование формата хранения vardecimal не включено.	Сценарии создания таблиц могут использоваться без изменения.
SQL Server 2005, для базы данных использование формата хранения vardecimal включено, но оно должно быть отключено в таблице подписчика.	Сценарии создания таблиц могут использоваться без изменения.
SQL Server 2005, в базе данных использование формата хранения vardecimal включено; оно должно быть включено и в таблице подписчика SQL Server 2005.	Сценарии создания таблиц можно изменить, чтобы включить использование формата vardecimal как для базы данных, так и для таблиц. Включить использование формата vardecimal в базе данных подписчика и таблицах подписчика можно также с помощью хранимых процедур, описанных ниже в разделе «Включение формата vardecimal».

Дополнительные сведения

Ниже приведен список дополнительных факторов, которые необходимо учитывать при работе с форматом хранения vardecimal.

Формат хранения vardecimal не влияет на операции массового импорта и экспорта (bcp).
Функция DATALENGTH не распознает формат хранения vardecimal и возвращает число байт, которое использовалось бы при хранении данных в фиксированном десятичном формате.
В редких случаях формат хранения vardecimal не позволит SQL Server использовать план запроса, оптимальный для данных в фиксированном десятичном формате.
Формат хранения vardecimal может использоваться с любым уровнем совместимости базы данных.
Если во время выполнения хранимой процедуры sp_tableoption в таблице нет столбцов типа decimal или numeric, метаданные таблицы меняются, чтобы показать, что в таблице используется формат хранения vardecimal. Если позже добавляются новые столбцы типа decimal, они хранятся в формате vardecimal. Для добавления или удаления столбцов таблицы, в которой используется формат хранения vardecimal, никакие особые методы не требуются.

Включение формата хранения vardecimal

Для включения формата хранения vardecimal или перевода данных в этот формат необходимо иметь следующие разрешения.

Для включения формата хранения vardecimal в базе данных требуется разрешение ALTER DATABASE на сервере.
Для перевода таблицы в формат хранения vardecimal требуется разрешение ALTER для таблицы.

Прежде чем включать формат vardecimal, необходимо убедиться в том, что в случае использования формата vardecimal размер таблицы уменьшится. Если заданная точность столбца требуется для большинства строк, то издержки, связанные с использованием формата хранения vardecimal, могут превысить экономию и привести к появлению таблицы большего размера. Прежде чем менять таблицу, можно оценить возможное уменьшение размера строк с помощью хранимой процедуры sp_estimated_rowsize_reduction_for_vardecimal. Если принято решение сменить формат хранения таблицы, включите использование формата vardecimal для базы данных, а затем для отдельных таблиц.

Включить использование формата хранения vardecimal для данных типа decimal можно с помощью хранимых процедур или SQL Server Management Studio, как показано ниже.

Выполните хранимую процедуру sp_db_vardecimal_storage_format, чтобы включить использование формата хранения vardecimal для базы данных, а затем хранимую процедуру sp_tableoption, чтобы включить использование формата vardecimal в соответствующих таблицах.
В Management Studio, чтобы включить использование формата хранения vardecimal в базе данных, используйте страницу Свойства базы данных. Для перевода таблицы на формат хранения vardecimal необходимо использовать хранимую процедуру sp_tableoption.

Идентификация таблиц, в которых используется формат хранения vardecimal

Чтобы определить, в каких таблицах базы данных используется формат хранения vardecimal, воспользуйтесь функцией OBJECTPROPERTY и найдите свойство TableHasVarDecimalStorageFormat.

В следующем примере возвращается значение 1, если в таблице Production.WorkOrderRouting используется формат хранения vardecimal, и значение 0, если этот формат не используется.

USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'), 
   'TableHasVarDecimalStorageFormat') ;
GO

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

USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
 WHERE OBJECTPROPERTY(object_id, 
   N'TableHasVarDecimalStorageFormat') = 1 ;
GO

Проблемы, связанные с отключением формата хранения vardecimal

Чтобы отключить формат хранения vardecimal для таблицы, необходимо перестроить таблицу в фиксированном десятичном формате. Это может значительно увеличить место на диске, занимаемое таблицей. Если места на диске недостаточно, операция перестроения закончится ошибкой. Поэтому, чтобы отключить формат хранения vardecimal, необходимо убедиться в том, что на диске достаточно места для SQL Server. Операция расширения также требует временного места на диске для хранения данных как в формате vardecimal, так и в обычном формате. Если на диске достаточно места для расширенной таблицы после расширения, но расширение завершается ошибкой только из-за отсутствия на диске достаточной временной области, можно выполнить расширение по частям, копируя строки таблицы в новую нерасширенную таблицу.

Чтобы отключить формат хранения vardecimal в базе данных сразу после ее изменения, удалите базу данных, а затем восстановите ее с резервной копии, созданной до того, как был включен формат хранения vardecimal.

При переносе базы данных из выпусков SQL Server 2005 Enterprise Edition, Developer Edition или Evaluation Edition в другой выпуск или более раннюю версию SQL Server, необходимо сначала открыть базу данных с помощью одной из требуемых редакций, отключить формат хранения vardecimal, а затем перенести базу данных. Попытка присоединить базу данных, содержащую таблицы в формате хранения vardecimal, к серверу, не поддерживающему этот формат, закончится ошибкой.

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