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

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).

Применимо для следующих объектов: SQL Server (SQL Server 2008 по текущую версию), База данных SQL Windows Azure (С первоначального выпуска по текущий выпуск).

Значок ссылки на раздел Cинтаксические обозначения в 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

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

    блокировка вставки строк

    Больше не поддерживается.

    Этот параметр не влияет на свойства блокировки 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.

    Большие определяемые пользователем типы (UDT) относятся к: С SQL Server 2008 по SQL Server 2014 включительно.

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

    Значения TRUE, ON или 1 означают, что для указанной таблицы включен формат хранения vardecimal. Значения FALSE, OFF или 0 означают, что для таблицы не включен формат хранения vardecimal. Формат хранения vardecimal можно включить, только если он включен для базы данных с помощью хранимой процедуры sp_db_vardecimal_storage_format. В SQL Server 2008 и последующих версиях формат хранения vardecimal рассматривается как устаревший. Вместо этого используйте сжатие ROW. Дополнительные сведения см. в разделе Сжатие данных. Значение по умолчанию — 0.

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

  • [ @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 в таблице.

Разрешения

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

Примеры

А.Хранение 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.

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

 
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)