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可以有下列其中一個值。

值 Description
AllowRowLocks 當為TRUE時，存取索引時允許數據列鎖定。 資料庫引擎會決定使用資料列鎖定的時機。 當為 FALSE 時，不會使用數據列鎖定。 預設值為TRUE。
AllowPageLocks 若為TRUE，存取索引時允許頁面鎖定。 資料庫引擎會決定使用頁面鎖定的時機。 當為 FALSE 時，不會使用頁面鎖定。 預設值為TRUE。
DisAllowRowLocks 若為 TRUE，則不會使用數據列鎖定。 當為 FALSE 時，存取索引時允許數據列鎖定。 資料庫引擎會決定使用資料列鎖定的時機。
DisAllowPageLocks 若為 TRUE，則不會使用頁面鎖定。 當為 FALSE 時，存取索引時允許頁面鎖定。 資料庫引擎會決定使用頁面鎖定的時機。

[ @OptionValue = ] 'value' 指定是否 啟用option_name 設定（TRUE、ON、yes 或 1） 或 disabled （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 權限。

範例

A. 在特定索引上設定選項

下列範例不允許數據表索引上的Customer頁面鎖定IX_Customer_TerritoryID。

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

B. 設定數據表上所有索引的選項

下列範例不允許數據表 Product 相關聯之所有索引的數據列鎖定。 在執行程式以顯示 語句結果之前和之後sp_indexoption，會sys.indexes查詢目錄檢視。

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. 在沒有叢集索引的數據表上設定選項

下列範例不允許在沒有叢集索引的數據表上鎖定頁面（堆積）。 在執行程式以顯示語句結果之前和之後sp_indexoption，會sys.indexes查詢目錄檢視。

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)