Статья
04/01/2012

sp_tableoption (Transact-SQL)

Устанавливает значения параметров для определяемых пользователем таблиц. Функция sp_tableoption может использоваться для контроля внутристрокового поведения таблиц со столбцами varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, а также с большими пользовательскими столбцами.

Важно!
Параметр text in row будет исключен в следующей версии SQL Server. Для хранения данных большого объема рекомендуется использовать типы данных varchar(max), nvarchar(max) и varbinary(max).

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

sp_tableoption [ @TableNamePattern = ] 'table' 
          , [ @OptionName = ] 'option_name' 
          ,[ @OptionValue =] 'value'

Аргументы

[ @TableNamePattern =] 'table'
Уточненное или неуточненное имя пользовательской таблицы базы данных. Если предоставлено полное имя таблицы, включая имя базы данных, в качестве последнего должно использоваться имя текущей базы данных. Параметры таблицы нельзя установить одновременно для нескольких таблиц. Аргумент table имеет тип nvarchar(776) и не имеет значения по умолчанию.

[ @OptionName = ] 'option_name'
Название параметра таблицы. Аргумент option_name имеет тип varchar(35) со значением по умолчанию NULL. Аргумент option_name может принимать одно из следующих значений.

Значение	Описание
table lock on bulk load	Если отключено (по умолчанию), то процесс массовой загрузки в пользовательских таблицах получает блокировку строк. Если включено, то процесс массовой загрузки в пользовательских таблицах получает блокировку массовых обновлений.
insert row lock	В SQL Server 2008 не поддерживается. Стратегия блокировки SQL Server — это блокировка строки с возможным переходом к блокировке страницы или таблицы. Этот параметр не влияет на свойства блокировки SQL Server и включается только для совместимости существующих сценариев и процедур.
text in row	При значении OFF или 0 (отключено по умолчанию) текущее поведение не меняется и в строке отсутствует блок больших двоичных объектов (BLOB). Если параметр указан и аргумент @OptionValue принимает значение ON или является целым числом от 24 до 7 000, то новые строки text, ntext или image сохраняются непосредственно в строке данных. Все существующие BLOB (большие двоичные объекты: text, ntext или image) при обновлении значения BLOB будут преобразованы в формат text in row. Дополнительные сведения см. ниже, в разделе «Примечания».
large value types out of row	1 = Столбцы varchar(max), nvarchar(max), varbinary(max), xml, а также большие столбцы определяемых пользователем типов хранятся в таблице вне строки с 16-байтовым указателем корня. 0 = Значения varchar(max), nvarchar(max), varbinary(max), xml, а также большие значения определяемых пользователем типов хранятся непосредственно в строке данных с ограничением в 8 000 байт до тех пор, пока значение помещается в записи. Если значение не умещается в записи, то указатель хранится в строке, а все остальное хранится вне строки в области хранения объектов LOB. Значение по умолчанию — 0.
vardecimal storage format	Значения TRUE, ON или 1 означают, что для указанной таблицы включен формат хранения vardecimal. Значения FALSE, OFF или 0 означают, что для таблицы не включен формат хранения vardecimal. Формат хранения vardecimal можно включить, только если он включен для базы данных с помощью хранимой процедуры sp_db_vardecimal_storage_format. Дополнительные сведения о формате хранения vardecimal см. в разделе Хранение десятичных данных в виде значений переменной длины. Для использования этого формата требуется SQL Server 2005 с пакетом обновления 2 (SP2). Формат хранения Vardecimal доступен только в выпусках Enterprise Edition, Developer Edition и Evaluation Edition сервера SQL Server. В SQL Server 2008 и последующих версиях для всех пользовательских баз данных включен формат хранения данных vardecimal. В SQL Server 2008 и последующих версиях формат хранения vardecimal рассматривается как устаревший. Вместо этого используйте сжатие ROW. Дополнительные сведения см. в разделе Создание сжатых таблиц и индексов. Значение по умолчанию — 0.

[ @OptionValue =] 'value'
Указывает, включен (TRUE, ON, yes или 1) или выключен (FALSE, OFF, no или 0) параметр option_name. Аргумент value имеет тип varchar(12) и не имеет значения по умолчанию. Аргумент value чувствителен к регистру.

Для параметра text in row допустимыми значениями являются 0, ON, OFF или целое число в диапазоне от 24 до 7 000. Если аргумент value имеет значение ON, ограничение по умолчанию равно 256 байт.

Значения кода возврата

0 (успешное завершение) или номер ошибки (неуспешное завершение)

Замечания

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

Параметр text in row процедуры sp_tableoption может быть включен или выключен только на таблицах, содержащих текстовые столбцы. Если таблица не содержит текстового столбца, в SQL Server происходит ошибка.

Когда параметр text in row включен, аргумент @OptionValue позволяет пользователям задать максимальный размер хранения в строке для BLOB. Значение по умолчанию равно 256 байт, но значения могут располагаться в диапазоне с 24 по 7 000 байт.

Строки text, ntext или image хранятся в строке данных при наличии следующих условий:

text in row доступен;
длина строки не превышает максимального предела, указанного в аргументе @OptionValue;
в строке данных достаточно места.

Если в строке данных хранятся строки BLOB, считывание и запись строк text, ntext или image может производиться так же быстро, как считывание и запись символьных и двоичных строк. SQL Server не требуется получать доступ к отдельным страницам для считывания или записи строк BLOB.

Если строка типа text, ntext или image превышает максимальный предел или доступное место в строке, в строке хранятся указатели. Условия для хранения строк типа BLOB в строке остаются действительными, несмотря на следующее. Для хранения указателей в строке данных должно быть достаточно места.

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

Существующие строки BLOB преобразуются через некоторое время после первого включения text in row. Строки преобразуются только при обновлении. Аналогично при увеличении предела параметра text in row строки text, ntext и image, уже содержащиеся в строке данных, не будут преобразованы, чтобы соответствовать новому ограничению, до тех пор, пока они не будут обновлены.

Примечание
Отключение параметра text in row или уменьшение предела параметра потребует преобразования всех BLOB; поэтому процесс может занять много времени, в зависимости от того, какое число строк BLOB необходимо преобразовать. Во время процесса преобразования таблица блокируется.

Отключение параметра text in row или уменьшение предела параметра потребует преобразования всех BLOB; поэтому процесс может занять много времени, в зависимости от того, какое число строк BLOB необходимо преобразовать. Во время процесса преобразования таблица блокируется.

Для табличной переменной, включая функцию, которая возвращает табличную переменную, параметр text in row включен автоматически и имеет значение параметра inline limit, по умолчанию равное 256. Этот параметр нельзя изменить.

text in row поддерживает функции TEXTPTR, WRITETEXT, UPDATETEXT и READTEXT. Пользователи могут считывать части BLOB с помощью функции SUBSTRING(), но при этом следует помнить, что внутристрочные текстовые указатели по длине и пределам чисел отличаются от других текстовых указателей. Дополнительные сведения см. в разделе Управление данными типа ntext, text и image.

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

Разрешения

Чтобы выполнить процедуру sp_tableoption, требуется разрешение ALTER на таблицу.

Примеры

А. Хранение XML-данных вне строки

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

USE AdventureWorks2008R2;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

Б. Включение формата хранения vardecimal для таблицы

В следующем примере в таблицу Production.WorkOrderRouting вносятся изменения, чтобы можно было хранить данные типа decimal в vardecimalstorage format.

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXEC sp_db_vardecimal_storage_format 'AdventureWorks2008R2', 'ON';
GO
USE AdventureWorks2008R2;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';