共用方式為

  • 發行項

sp_indexoption (Transact-SQL)

針對使用者自訂叢集和非叢集索引，或不含叢集索引的資料表，來設定鎖定選項值。

SQL Server Database Engine 會自動選擇頁面、資料列或資料表層級的鎖定。您不需要手動設定這些選項。sp_indexoption 適用於確實瞭解永遠適用特定鎖定類型的專家級使用者。

重要事項重要事項

下一版的 Microsoft 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 時，在存取索引時，允許資料列鎖定。Database Engine 會決定使用資料列鎖定的時機。當設為 FALSE 時，不會使用資料列鎖定。預設值是 TRUE。

    AllowPageLocks

    當設為 TRUE 時，在存取索引時，允許頁面鎖定。Database Engine 會決定使用頁面鎖定的時機。當設為 FALSE 時，不會使用頁面鎖定。預設值是 TRUE。

    DisAllowRowLocks

    當設為 TRUE 時，不會使用資料列鎖定。當設為 FALSE 時，在存取索引時，允許資料列鎖定。Database Engine 會決定使用資料列鎖定的時機。

    DisAllowPageLocks

    當設為 TRUE 時，不會使用頁面鎖定。當設為 FALSE 時，在存取索引時，允許頁面鎖定。Database Engine 會決定使用頁面鎖定的時機。

  • [@OptionValue =] 'value'
    指定啟用 (TRUE、ON、yes 或 1) 或停用 (FALSE、OFF、no 或 0) option_name 設定。value 是 varchar(12)，沒有預設值。

傳回碼值

0 (成功) 或大於零 (失敗)

備註

不支援 XML 索引。如果指定了 XML 索引，或指定了資料表名稱，但不含索引名稱，且資料表包含 XML 索引，陳述式便會失敗。若要設定這些選項，請改用 ALTER INDEX。

若要顯示目前資料列和頁面鎖定屬性，請使用 INDEXPROPERTY 或 sys.indexes 目錄檢視。

  • 如果 AllowRowLocks = TRUE 或 DisAllowRowLocks = FALSE，且 AllowPageLocks = TRUE 或 DisAllowPageLocks = FALSE，當取索引時，允許資料列、頁面和資料表層級鎖定。Database Engine 會選擇適當的鎖定，且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。如需詳細資訊，請參閱＜鎖定擴大 (Database Engine)＞。

如果 AllowRowLocks = FALSE 或 DisAllowRowLocks = TRUE 且 AllowPageLocks = FALSE 或 DisAllowPageLocks = TRUE，當存取索引時，只允許資料表層級的鎖定。

如果指定資料表名稱，但不含索引，設定便適用於這份資料表的所有索引。當基礎資料表沒有叢集索引 (也就是說，它是一個堆積) 時，就會依照下列方式來套用設定：

  • 當 AllowRowLocks 或 DisAllowRowLocks 設為 TRUE 或 FALSE 時，會將設定套用在堆積及任何相關聯的非叢集索引上。

  • 當 AllowPageLocks 選項設為 TRUE 或 DisAllowPageLocks 設為 FALSE 時，會將設定套用在堆積及任何相關聯的非叢集索引上。

  • 當 AllowPageLocks 選項設為 FALSE 或 DisAllowPageLocks 設為 TRUE 時，會將設定完整套用在非叢集索引上。也就是說，在非叢集索引上，不允許使用任何頁面鎖定。在堆積上，不允許的鎖定只有頁面的共用 (S)、更新 (U) 和獨佔 (X) 鎖定。Database Engine 仍能取得意圖頁面鎖定 (IS、IU 或 IX)，供內部使用。

如需有關設定索引之鎖定資料粒度的詳細資訊，請參閱＜自訂索引的鎖定＞。

權限

需要資料表的 ALTER 權限。

範例

A. 設定特定索引的選項

下列範例會禁止 Customer 資料表 IX_Customer_TerritoryID 索引的頁面鎖定。

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

B. 設定資料表所有索引的選項

下列範例會禁止 Product 資料表所有相關聯索引的資料列鎖定。在執行 sp_indexoption 程序來顯示陳述式結果之前和之後，都會查詢 sys.indexes 目錄檢視。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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