sp_tableoption (Transact-SQL)

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Внимание

Текст в строке будет удален в будущей версии SQL Server. Для хранения данных больших значений рекомендуется использовать типы данных varchar(max),nvarchar(max) и varbinary(max).

Соглашения о синтаксисе Transact-SQL

Синтаксис

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Аргументы

[ @TableNamePattern = ] N'TableNamePattern'

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

[ @OptionName = ] 'OptionName'

Имя параметра таблицы. @OptionName — varchar(35) и может быть одним из следующих значений.

значение Описание
table lock on bulk load Если отключено (по умолчанию), то процесс массовой загрузки в пользовательских таблицах получает блокировку строк. Если включено, то процесс массовой загрузки в пользовательских таблицах получает блокировку массовых обновлений.
insert row lock Больше не поддерживается.

Этот параметр не влияет на поведение блокировки SQL Server и включается только для совместимости существующих скриптов и процедур.
text in row Если OFF или 0 (отключено, значение по умолчанию) оно не изменяет текущее поведение и не имеет большого двоичного объекта в строке.

При указании и @OptionValue ( ON включено) или целочисленное значение из 24 7000строки данных, нового текста, ntext или изображений хранятся непосредственно в строке данных. Все существующие данные BLOB (двоичный большой объект: текст, ntext или изображение) изменяются на текст в формате строки при обновлении значения BLOB. Дополнительные сведения см. в подразделе "Примечания".
large value types out of row 1 = varchar(max), nvarchar(max), varbinary(max), xml и большие пользовательские столбцы типа (UDT) в таблице хранятся вне строки с 16-байтовым указателем на корень.

0 = varchar(max), nvarchar(max), varbinary(max), xml и большие значения определяемых пользователем значений хранятся непосредственно в строке данных до предела в 8000 байт и до тех пор, пока значение может быть вместимо в запись. Если значение не соответствует записи, указатель хранится в строке, а остальные хранятся вне строки в хранилище бизнес-объектов. Значение по умолчанию — 0.

Большой определяемый пользователем тип (UDT) применяется к SQL Server 2008 (10.0.x) и более поздним версиям.

TEXTIMAGE_ON Используйте параметр CREATE TABLE, чтобы указать расположение для хранения больших типов данных.
Формат хранилища vardecimal Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

[ @OptionValue = ] 'OptionValue'

Указывает, включена ли @OptionName (TRUE, ONили) или 1отключена (FALSE, OFFили 0). @OptionValue — varchar(12), без значения по умолчанию. @OptionValue не учитывает регистр.

Для текста в строке допустимые значения параметров: 0, ONOFFили целое число, начиная с 24 7000строки. Если @OptionValue , ONограничение по умолчанию составляет 256 байт.

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

0 (успешно) или номер ошибки (сбой).

Замечания

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

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

Если включен параметр текста в строке, параметр @OptionValue позволяет пользователям указывать максимальный размер, хранящийся в строке большого двоичного объекта. Значение по умолчанию равно 256 байт, но значения могут располагаться в диапазоне с 24 по 7 000 байт.

Строки текста, ntext или изображения хранятся в строке данных, если применяются следующие условия:

  • Текст в строке включен.
  • Длина строки короче ограничения, указанного в @OptionValue.
  • В строке данных достаточно места.

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

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

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

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

Примечание.

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

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

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

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

Если вы преобразуете существующий столбец типа данных бизнес-объекта (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 разрешение на таблицу.

Примеры

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

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

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

B. Включение формата хранилища vardecimal в таблице

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

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 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
   'vardecimal storage format', 'ON';

Связанный контент