通过

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、、yes或1）还是禁用（FALSE、ONOFF、no或0）。 @OptionValue 为 varchar（12），没有默认值。

返回代码值

0（成功）或 > 0（失败）。

注解

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

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

  • 在访问索引时，允许行级别、页级和表级锁（或）访问索引。AllowRowLocks = TRUE DisAllowRowLocks = FALSEAllowPageLocks = TRUE DisAllowPageLocks = FALSE 数据库引擎将选择相应的锁，并且可以将锁从行锁或页锁升级到表锁。

访问索引时AllowRowLocks = FALSE或或DisAllowRowLocks = TRUEAllowPageLocks = FALSE或访问DisAllowPageLocks = TRUE索引时，仅允许表级锁。

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

  • 当或设置为或设置为TRUE该FALSEDisAllowRowLocks设置时AllowRowLocks，该设置将应用于堆和任何关联的非聚集索引。

  • 当选项设置为TRUE或DisAllowPageLocks设置为FALSE该选项时AllowPageLocks，该设置将应用于堆和任何关联的非聚集索引。

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

权限

要求对表具有 ALTER 权限。

示例

A. 在特定索引上设置选项

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

USE AdventureWorks2022;
GO

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

相关内容