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 — это блокировка строки с возможным переходом к блокировке страницы или таблицы. Этот параметр не влияет на свойства блокировки 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. В SQL Server 2008 и последующих версиях формат хранения vardecimal рассматривается как устаревший. Вместо этого используйте сжатие ROW. Дополнительные сведения см. в разделе Сжатие данных. Значение по умолчанию — 0.

• [ @OptionValue =] 'value'
  Указывает, включен (TRUE, ON или 1) или выключен (FALSE, OFF или 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 включен автоматически с предусмотренным по умолчанию встроенным пределом 256. Этот параметр нельзя изменить.

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

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

Если выполняется преобразование столбца существующего типа данных LOB (text, ntext или image) в типы данных от малых до больших значений (varchar(max), nvarchar(max) или varbinary(max)), и большинство инструкций не ссылаются на столбцы типов данных больших значений в вашей среде, может быть целесообразно заменить large_value_types_out_of_row на 1 для повышения производительности. При изменении значения параметра large_value_types_out_of_row существующие значения varchar(max), nvarchar(max), varbinary(max) и xml не преобразуются немедленно. Порядок хранения строк меняется при их дальнейшем обновлении. Все новые значения, вставленные в таблицу, сохраняются в соответствии с действующим параметром таблицы. Для получения немедленных результатов следует либо создать копию данных, а затем повторно заполнить таблицу после изменения параметра large_value_types_out_of_row, либо обновить каждый столбец с типами данных от малых до больших значений, чтобы хранение строк изменилось с табличным параметром. Также можно пересоздать индексы таблицы после обновления или повторного заполнения, чтобы сжать таблицу.

Разрешения

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

Примеры

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

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

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

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

В следующем примере в таблицу Production.WorkOrderRouting вносятся изменения, чтобы можно было хранить данные типа decimal в vardecimal storage 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 'AdventureWorks2012', 'ON';
GO
USE AdventureWorks2012;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';

См. также

Справочник

sys.tables (Transact-SQL)

OBJECTPROPERTY (Transact-SQL)

Системные хранимые процедуры (Transact-SQL)

Хранимые процедуры ядра СУБД (Transact-SQL)