sp_indexoption (Transact-SQL)

项目
03/12/2024

为用户定义的聚集索引和非聚集索引或没有聚集索引的表设置锁选项值。

SQL Server 数据库引擎会自动选择页、行或表级锁定。您不必手动设置这些选项。 sp_indexoption 提供给具有确定性地知道特定类型的锁始终合适的专家用户。

重要

在 SQL Server的未来版本中将删除此功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。请改用 ALTER INDEX（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）或已禁用（FAL标准版、OFF、否或 0）。值为 varchar（12），没有默认值。

返回代码值

0（成功）或大于 0（失败）

注解

不支持 XML 索引。如果指定 XML 索引，或指定了不包含索引名的表名，且该表包含 XML 索引，则该语句将失败。若要设置这些选项，请改用 ALTER INDEX 。

若要显示当前行和页面锁定属性，请使用 INDEXPROPERTY 或 sys.indexes 目录视图。

当 AllowRowLocks = TRUE 或 DisAllowRowLocks = FAL标准版，AllowPageLocks = TRUE 或 DisAllowPageLocks = FAL标准版 时，访问索引时，允许使用行、页和表级锁。 数据库引擎将选择相应的锁，并且可以将锁从行锁或页锁升级到表锁。

当 AllowRowLocks = FAL标准版或 DisAllowRowLocks = TRUE 且 AllowPageLocks = FAL标准版或 DisAllowPageLocks = TRUE 时，仅允许表级锁。

如果指定表名时不包含索引，则设置将应用于该表的所有索引。如果基础表没有聚集索引（即它是一个堆），则设置按如下方式应用：

当 AllowRowLocks 或 DisAllowRowLocks 设置为 TRUE 或 FAL标准版时，该设置将应用于堆和任何关联的非聚集索引。
当 AllowPageLocks 选项设置为 TRUE 或 DisAllowPageLocks 设置为 FAL标准版时，该设置将应用于堆和任何关联的非聚集索引。
当 AllowPageLocks 选项设置为 FAL标准版或 DisAllowPageLocks 设置为 TRUE 时，该设置将完全应用于非聚集索引。也就是说，对非聚集索引禁用所有页锁。在堆上，只允许对页禁用共享 (S) 锁、更新 (U) 锁和排他 (X) 锁。数据库引擎仍然可以获取意向页锁（IS、IU 或 IX），供内部使用。

权限

需要对表的 ALTER 权限。

示例

A. 对特定索引设置选项

以下示例禁止对表上的Customer索引进行页锁IX_Customer_TerritoryID。

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)