Данные в строке
Типы больших значений размером от небольшого до среднего (varchar(max), nvarchar(max), varbinary(max) и xml) и типы данных LOB (text, ntext и image) могут храниться в строке данных. Это управляется двумя параметрами в системной хранимой процедуре sp_tableoption: параметром LARGE VALUE TYPES OUT OF ROW для типа больших значений и параметром TEXT IN ROW для типов больших объектов. Их лучше всего использовать для таблиц, в которых значения любого из вышеуказанных типов данных обычно считываются и записываются в одной единице измерения, и большинство инструкций, содержащих ссылки на таблицу, обращаются к этому типу данных. В зависимости от характеристик интенсивности использования и рабочей нагрузки хранение данных в строках может быть неоптимальным.
.gif "Важное примечание") Важно! |
|---|
Параметр TEXT IN ROW не будет использоваться в следующей версии SQL Server. Не рекомендуется использовать его в новых разработках и постараться внести изменения в приложения, которые в настоящее время используют этот параметр. Кроме того, следует хранить объемные данные в типах varchar(max), nvarchar(max) или varbinary(max). Для управления режимом в строке и вне строки этих типов данных используйте параметр LARGE VALUE TYPES OUT OF ROW. |
Если параметру TEXT IN ROW не присвоено значение ON или конкретное ограничение «в строке», строки text, ntext и image являются объемными символьными или двоичными строками (до 2 ГБ), хранимыми вне строки данных. Строка данных содержит только 16-байтный текстовый указатель на корневой узел дерева, составленного из внутренних указателей. Эти указатели задают соответствие со страницами, в которых хранятся фрагменты строк. Дополнительные сведения о хранении строк text, ntext и image см. в разделе Использование типов данных text и image.
Предусмотрена возможность задания параметра TEXT IN ROW для таблиц, содержащих столбцы типов данных LOB. Также можно задать предельное значение для параметра TEXT IN ROW в диапазоне от 24 до 7 000 байт.
Аналогично, если параметру LARGE VALUE TYPES OUT OF ROW не присвоено значение ON, столбцы varchar(max), nvarchar(max), varbinary(max) и xml при возможности хранятся в строке данных. В этом случае компонент SQL Server Database Engine пытается подобрать конкретное значение, если это возможно; в противном случае он принудительно выносит значения за пределы строки. Если параметру LARGE VALUE TYPES OUT OF ROW присвоено значение ON, значения хранятся вне строки, а в записи хранится только 16-байтный текстовый указатель.
.gif "Примечание") Примечание |
|---|
Предельным объемом для хранения больших типов-значений является 8 000 байт, если параметр LARGE VALUE TYPES OUT OF ROW имеет значение OFF. В отличие от параметра TEXT IN ROW, для столбцов таблицы нельзя задавать ограничение «в строке». |
Когда таблица настроена для хранения типа больших значений или LOB непосредственно в строке данных, фактические значения столбцов находятся в строке, если выполняется любое из следующих условий.
Длина строки меньше ограничения, заданного для столбцов text, ntext и image.
В строке данных достаточно места для хранения строки.
Если значение столбца с типом больших значений или LOB хранится в строке данных, компонент Database Engine не должен обращаться к отдельной странице или набору страниц для считывания или записи в символьную или двоичную строку. Это делает операции считывания и записи в строки «в строке» практически такой же операцией, что и считывание и запись в строки varchar, nvarchar или varbinary ограниченного размера. Аналогично, если значения хранятся вне строки, компонент Database Engine выполняет дополнительное считывание или запись в страницу.
Если строка не превышает предельное значение параметра TEXT IN ROW или доступное пространство в строке, то для типов данных LOB набор указателей, в других случаях хранимых в корневом узле дерева указателей, записывается в строку. Указатели записываются в строку, если выполняется любое из следующих условий:
объем дискового пространства, требуемого для хранения указателей, меньше заданного в параметре TEXT IN ROW предельного значения;
в строке данных достаточно места для хранения указателей.
Если указатели перемещены из корневого узла в саму строку, компонент Database Engine не обязательно должен использовать корневой узел. Это позволяет избежать необходимости доступа к страницам при считывании или записи в строку и тем самым повысить производительность.
Если используются корневые узлы, они хранятся в качестве одного из фрагментов строки в странице LOB и могут содержать до пяти внутренних указателей. Компоненту Database Engine требуется 72 байта в строке для хранения пяти указателей строки «в строке». Если в строке недостаточно места для хранения указателей и параметр TEXT IN ROW имеет значение ON или параметр LARGE VALUE TYPES OUT OF ROW имеет значение OFF, компоненту Database Engine, возможно, потребуется выделить страницу объемом 8 КБ для их хранения. Если длина данных значения превышает 40 200 байт, необходимо более пяти указателей «в строке», при этом только 24 байта хранятся в основной строке, и выделяется дополнительная страница данных в пространстве для хранения LOB.
Объемные строки хранятся в строке аналогично строкам переменной длины. Компонент Database Engine сортирует столбцы по убыванию размера и выносит значения за пределы строки до тех пор, пока оставшиеся столбцы не будут умещаться в странице данных (8 КБ).
Включение и отключение параметра large value types out of row
Параметр LARGE VALUE TYPES OUT OF ROW для таблицы можно включить хранимой процедурой sp_tableoption следующим образом:
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
При указании значения OFF ограничитель длины строк для столбцов varchar(max), nvarchar(max), varbinary(max) и xml устанавливается в значение в 8 000 байт. В строке хранится только 16-байтный корневой указатель, а само значение содержится в пространстве для хранения LOB. Рекомендуем присваивать этому параметру значение ON для таблиц, в которых большинство инструкций не ссылается на столбцы большого типа-значения. Внестрочное хранение этих столбцов подразумевает, что можно уместить большее количество строк на страницу, тем самым уменьшая количество операций ввода-вывода, затрачиваемых на сканирование таблицы.
Если этому параметру присвоено значение OFF, многие строки могут в итоге храниться в самой строке, потенциально уменьшая количество строк данных, помещающихся в каждой странице. Если большинство инструкций, содержащих ссылки на таблицу, не обращаются к столбцам типа varchar(max), nvarchar(max), varbinary(max) и xml, уменьшение количества строк на странице может привести к увеличению количества страниц, которые необходимо прочитать при обработке запросов. Уменьшение количества строк на страницу потенциально может увеличить количество страниц, которые должны быть отсканированы, если оптимизатор не найдет подходящего индекса.
Также можно использовать хранимую процедуру sp_tableoption для отключения параметра OUT OF ROW:
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
При изменении значения параметра LARGE VALUE TYPES OUT OF ROW текущие значения типа varchar(max), nvarchar(max), varbinary(max) и xml не будут преобразованы моментально. Порядок хранения строк меняется при их дальнейшем обновлении. Все новые значения, вставленные в таблицу, сохраняются в соответствии с действующим параметром таблицы.
Чтобы проверить значение параметра LARGE VALUE TYPES OUT OF ROW для конкретной таблицы, выполните запрос к столбцу large_value_types_out_of_row представления каталога sys.tables. Значение этого столбца равно 0, если в таблице не включен параметр LARGE VALUE TYPES OUT OF ROW, и 1, если типы больших значений хранятся вне строк.
Включение и отключение параметра TEXT IN ROW
Параметр TEXT IN ROW для таблицы можно включить хранимой процедурой sp_tableoption следующим образом:
sp_tableoption N'MyTable', 'text in row', 'ON'
При необходимости можно задать предельное значение в диапазоне от 24 до 7 000 байт для длины строки text, ntext и image, которую можно хранить в следующей строке данных:
sp_tableoption N'MyTable', 'text in row', '1000'
Если вместо конкретного предела будет указано значение ON, пределом по умолчанию будет 256 байт. Это значение по умолчанию позволяет использовать большую часть преимуществ по производительности, предоставляемых параметром TEXT IN ROW. Хотя обычно не требуется устанавливать значение меньше 72, слишком большое значение также не рекомендуется использовать. Особенно это важно для таблиц, в которых большинство инструкций не содержат ссылки на столбцы text, ntext и image или в которых содержится несколько столбцов text, ntext и image.
Если задано большое ограничение на значение параметра TEXT IN ROW и в самой строке хранится много строк, можно существенно уменьшить количество строк данных, помещающихся в каждой странице. Если большинство инструкций, содержащих ссылки на таблицу, не обращаются к столбцам text, ntext или image, уменьшение количества строк в странице может увеличить количество страниц, которые должны быть считаны при обработке запросов. Уменьшение количества строк на страницу потенциально может увеличить размер индексов и страниц, которые должны быть отсканированы, если оптимизатор не найдет подходящего индекса. Значения по умолчанию в 256 байт для ограничения TEXT IN ROW достаточно, чтобы обеспечить хранение небольших строк и корневых текстовых указателей в строках, но недостаточно для такого снижения количества строк на страницу, чтобы повлиять на производительность.
Параметру TEXT IN ROW автоматически присваивается значение 256 байт для переменных типа table и таблиц, возвращаемых пользовательскими функциями. Это значение невозможно изменить.
Также можно использовать хранимую процедуру sp_tableoption для отключения этого параметра, задав в ней в качестве его значения OFF или 0:
sp_tableoption N'MyTable', 'text in row', 'OFF'
Чтобы проверить значение параметра TEXT IN ROW для конкретной таблицы, выполните запрос к столбцу text_in_row_limit представления каталога sys.tables. Значение этого столбца равно 0, если параметр TEXT IN ROW для таблицы отключен, и больше 0, если задано ограничение «в строке».
Результаты использования параметра TEXT IN ROW
Параметр TEXT IN ROW оказывает следующее влияние.
После включения параметра TEXT IN ROW можно применять инструкции TEXTPTR, READTEXT, UPDATETEXT и WRITETEXT для считывания и редактирования частей любого значения text, ntext или image, сохраненного в таблице. В инструкциях SELECT можно считывать всю строку text, ntext или image или использовать функцию SUBSTRING для считывания частей строки. Все инструкции INSERT и UPDATE, которые содержат ссылки на таблицу, должны задавать полные строки и не могут вносить изменения в часть строки text, ntext или image.
При первом включении параметра TEXT IN ROW существующие строки text, ntext и image не преобразуются сразу в строки «в строке». Это происходит только в случае их последующего обновления. Все строки text, ntext и image после включения параметра TEXT IN ROW вставляются как строки «в строке».
Отключение параметра TEXT IN ROW может быть ресурсоемкой регистрируемой в журнале операцией. При этом блокируется таблица и все строки «в строке» text, ntext и image преобразуются в обычные строки text, ntext и image. Продолжительность выполнения команды и объем редактируемых данных зависит от того, сколько строк text, ntext и image необходимо преобразовать из строк «в строке» в обычные строки.
Параметр TEXT IN ROW не оказывает влияния на работу ODBC-драйвера собственного клиента SQL Server или поставщика OLE DB для собственного клиента SQL Server, за исключением ускорения доступа к данным типа text, ntext и image.
Функции работы с типами данных text и image из библиотеки DB-Library, такие как dbreadtext и dbwritetext, нельзя использовать для таблицы после включения параметра TEXT IN ROW.