sp_indexoption (Transact-SQL)

  • Статья

Применимо к:SQL Server

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

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

Внимание

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

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

Синтаксис

  
sp_indexoption [ @IndexNamePattern = ] 'table_or_index_name'   
    , [ @OptionName = ] 'option_name'   
    , [ @OptionValue = ] 'value'

Аргументы

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

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

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

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

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

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

Замечания

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

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

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

При доступе к индексу допускается только блокировка уровня таблицы, если 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

C. Настройка параметра на таблицу, не имеющую кластеризованного индекса

Следующий пример запрещает блокировки страниц на таблицу, не имеющую кластеризованного индекса (куча). Представление 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)