sp_indexoption (Transact-SQL)

Область применения: SQL Server

Устанавливает блокирующие значения параметров для определенных пользователем кластеризованных и некластеризованных индексов или таблиц, не имеющих кластеризованного индекса.

SQL Server ядро СУБД автоматически делает выбор блокировки уровня страницы, строки или таблицы. Эти параметры не нужно задавать вручную. sp_indexoption предоставляется для экспертов, которые знают, что определенный тип блокировки всегда подходит.

Внимание

Эта функция будет удалена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Вместо этого используйте ALTER INDEX.

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

Синтаксис

sp_indexoption
    [ @IndexNamePattern = ] N'IndexNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Аргументы

[ @IndexNamePattern = ] N'IndexNamePattern'

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

[ @OptionName = ] 'OptionName'

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

значение	Описание
AllowRowLocks	Если TRUEпри доступе к индексу разрешены блокировки строк. Ядро СУБД определяет, когда используются блокировки строк. Если FALSEблокировки строк не используются. Значение по умолчанию — TRUE.
AllowPageLocks	Если TRUEпри доступе к индексу разрешены блокировки страниц. Ядро СУБД определяет, когда используются блокировки страниц. Если FALSEблокировки страниц не используются. Значение по умолчанию — TRUE.
DisAllowRowLocks	Если TRUEблокировки строк не используются. Если FALSEпри доступе к индексу разрешены блокировки строк. Ядро СУБД определяет, когда используются блокировки строк.
DisAllowPageLocks	Если TRUEблокировки страниц не используются. Если FALSEпри доступе к индексу разрешены блокировки страниц. Ядро СУБД определяет, когда используются блокировки страниц.

[ @OptionValue = ] 'OptionValue'

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

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

0 (успешно) или > 0 (сбой).

Замечания

XML-индексы не поддерживаются. Если указаны XML-индексы, или имя таблицы указано без имени индекса, и таблица содержит XML-индекс, то инструкция завершается ошибкой. Чтобы задать эти параметры, используйте ALTER INDEX .

Чтобы отобразить текущие свойства блокировки строк и страниц, используйте представление каталога INDEXPROPERTY или sys.indexes .

• Блокировки на уровне строк, уровня страницы и таблицы разрешены при доступе к индексу, когда AllowRowLocks = TRUE или DisAllowRowLocks = FALSE, DisAllowPageLocks = FALSEили AllowPageLocks = TRUE . Ядро СУБД выбирает соответствующую блокировку и может передавать блокировку из строки или блокировки страницы на блокировку таблицы.

При доступе к индексу допускается только блокировка на уровне таблицы, когда AllowRowLocks = FALSE или DisAllowRowLocks = TRUE илиAllowPageLocks = FALSE.DisAllowPageLocks = TRUE

Если имя таблицы указано без индекса, то настройка применяется ко всем индексам этой таблицы. Если базовая таблица не имеет кластеризованного индекса (т. е. куча), параметры применяются следующим образом:

• Если AllowRowLocks или DisAllowRowLocks задано TRUE значение или FALSE, параметр применяется к куче и любым связанным некластеризованным индексам.

• Если AllowPageLocks для параметра задано TRUE значение или DisAllowPageLocks задано FALSEзначение, параметр применяется к куче и любым связанным некластеризованным индексам.

• Если AllowPageLocks параметр задан FALSE или DisAllowPageLocks задан TRUE, параметр полностью применяется к некластеризованным индексам. Таким образом, все блокировки страниц не допускаются для некластеризованных индексов. В куче, для страницы недопустимы только совмещаемая (S), обновления (U) и монопольная (X) блокировки. Ядро СУБД по-прежнему может получить блокировку страницы намерений (IS, IU или IX) для внутренних целей.

Разрешения

Необходимо разрешение ALTER для таблицы.

Примеры

А. Установка параметра для определенного индекса

В следующем примере запрещается блокировка страницы индекса IX_Customer_TerritoryID в Customer таблице.

USE AdventureWorks2022;
GO

EXEC sp_indexoption N'Sales.Customer.IX_Customer_TerritoryID',
    N'disallowpagelocks',
    TRUE;

B. Установка параметра для всех индексов в таблице

Следующий пример демонстрирует блокировки строк на все индексы, связанные с таблицей Product. Представление каталога sys.indexes запрашивается до и после выполнения процедуры sp_indexoption для демонстрации результата выполнения инструкции.

USE AdventureWorks2022;
GO

--Display the current row and page lock options for all indexes on the table.
SELECT name,
    type_desc,
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'Production.Product');
GO

-- Set the disallowrowlocks option on the Product table.
EXEC sp_indexoption N'Production.Product',
    N'disallowrowlocks',
    TRUE;
GO

--Verify the row and page lock options for all indexes on the table.
SELECT name,
    type_desc,
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'Production.Product');
GO

В. Установка параметра в таблице без кластеризованного индекса

Следующий пример запрещает блокировки страниц на таблицу, не имеющую кластеризованного индекса (куча). Представление sys.indexes каталога запрашивается до и после sp_indexoption выполнения процедуры, чтобы отобразить результаты инструкции.

USE AdventureWorks2022;
GO

--Display the current row and page lock options of the table.
SELECT OBJECT_NAME(object_id) AS [Table],
    type_desc,
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = N'DatabaseLog';
GO

-- Set the disallowpagelocks option on the table.
EXEC sp_indexoption DatabaseLog,
    N'disallowpagelocks',
    TRUE;
GO

--Verify the row and page lock settings of the table.
SELECT OBJECT_NAME(object_id) AS [Table],
    allow_row_locks,
    allow_page_locks
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = N'DatabaseLog';
GO

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

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