共用方式為

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），而且可以是下列其中一個值。

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

[ @OptionValue = ] 'OptionValue'

指定是否開啟@OptionName設定 （、 、 yes或 ） 或1已停用 （TRUEFALSE、OFFONno 、 或 0） 。 @OptionValue為 varchar（12），沒有預設值。

傳回碼值

0 (成功) 或 > 0 (失敗)。

備註

不支援 XML 索引。 如果指定了 XML 索引，或未指定任何索引名稱且數據表包含 XML 索引的數據表名稱，語句就會失敗。 若要設定這些選項，請改用 ALTER INDEX 。

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

  • 當 或 DisAllowRowLocks = FALSEAllowPageLocks = TRUE DisAllowPageLocks = FALSE或時存取索引AllowRowLocks = TRUE時，允許數據列層級、頁面層級和數據表層級鎖定。 資料庫引擎會選擇適當的鎖定，且可以將鎖定從資料列或頁面鎖定擴大到資料表鎖定。

在或 或 DisAllowRowLocks = TRUE AllowPageLocks = FALSE DisAllowPageLocks = TRUE時存取索引AllowRowLocks = FALSE時，只允許數據表層級鎖定。

如果未指定數據表名稱且沒有索引，則設定會套用至該數據表上的所有索引。 當基礎表沒有叢集索引（也就是堆積）時，就會套用設定，如下所示：

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

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

  • 當 選項設定為 或 DisAllowPageLocks 設定FALSE為 TRUE時AllowPageLocks，設定會完全套用至非叢集索引。 也就是說，非叢集索引上不允許所有頁面鎖定。 在堆積上，不允許頁面的共用 （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

相關內容