sp_indexoption (Transact-SQL)

適用対象:SQL Server

ユーザー定義のクラスター化インデックスと非クラスター化インデックス、またはクラスター化インデックスのないテーブルのロック オプション値を設定します。

SQL Server データベース エンジンは、ページ レベル、行レベル、またはテーブル レベルのロックを自動的に選択します。 これらのオプションを手動で設定する必要はありません。 sp_indexoptionは、特定の種類のロックが常に適切であることを確実に知っている専門家のユーザーに提供されます。

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には、次のいずれかの値を指定できます。

[ @OptionValue = ] 'value'option_name設定が有効 (TRUE、ON、はい、または 1) か無効 (FAL Standard Edition、OFF、no、または 0) かを指定します。 値 は varchar(12) で、既定値はありません。

リターン コードの値

0 (成功) または 0 より大きい (失敗)

解説

XML インデックスはサポートされていません。 XML インデックスが指定されているか、インデックス名のないテーブル名が指定されていて、テーブルに XML インデックスが含まれている場合、ステートメントは失敗します。 これらのオプションを設定するには、代わりに ALTER INDEX を使用します。

現在の行とページロックのプロパティを表示するには、INDEXPROPERTY または sys.indexes カタログ ビューを使用します。

• 行、ページ、テーブル レベルのロックは、AllowRowLocks = TRUE または DisAllowRowLocks = FAL Standard Edition、AllowPageLocks = TRUE または DisAllowPageLocks = FAL Standard Edition の場合にインデックスにアクセスするときに許可されます。 データベース エンジンは適切なロックを選択し、行ロックまたはページ ロックをテーブル ロックにエスカレートすることができます。

AllowRowLocks = FAL Standard Edition または DisAllowRowLocks = TRUE、AllowPageLocks = FAL Standard Edition または DisAllowPageLocks = TRUE の場合、インデックスにアクセスする場合は、テーブル レベルのロックのみが許可されます。

テーブル名をインデックスなしで指定すると、設定はそのテーブルのすべてのインデックスに適用されます。 基になるテーブルにクラスター化インデックスがない (つまりヒープである) 場合、設定は次のように適用されます。

• AllowRowLocks または DisAllowRowLocks を TRUE または FAL に設定するとStandard Editionヒープおよび関連付けられている非クラスター化インデックスに設定が適用されます。

• AllowPageLocks オプションが TRUE に設定されている場合、または DisAllowPageLocks が FAL Standard Edition に設定されている場合、ヒープおよび関連付けられている非クラスター化インデックスに設定が適用されます。

• AllowPageLocks オプションが FAL Standard Edition または DisAllowPageLocks が TRUE に設定されている場合、この設定は非クラスター化インデックスに完全に適用されます。 つまり、非クラスター化インデックスでは、すべてのページ ロックが許可されません。 ヒープで許可されないのは、ページに対する共有 (S)、更新 (U)、および排他 (X) ロックのみです。 データベース エンジンでは内部目的用にインテント ページ ロック (IS、IU、または IX) を引き続き取得できます。

アクセス許可

テーブルに対する ALTER 権限が必要です。

例

A. 特定のインデックスにオプションを設定する

次の例では、テーブルのインデックスに対する IX_Customer_TerritoryID ページ ロックを Customer 禁止します。

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)