sp_indexoption (Transact-SQL)

適用対象: SQL Server

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

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

Transact-SQL 構文表記規則

構文

sp_indexoption
    [ @IndexNamePattern = ] N'IndexNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

引数

[ @IndexNamePattern = ] N'IndexNamePattern'

ユーザー定義テーブルまたはインデックスの修飾名または非修飾名。 @IndexNamePattern は nvarchar(1035) で、既定値はありません。 引用符は、修飾インデックスまたはテーブル名が指定されている場合にのみ必要です。 データベース名も含めてフル パスで指定した場合は、そのデータベース名は現在のデータベース名である必要があります。 インデックスのないテーブル名が指定されている場合、クラスター化インデックスが存在しない場合は、そのテーブルのすべてのインデックスとテーブル自体に対して指定されたオプション値が設定されます。

[ @OptionName = ] 'OptionName'

インデックス オプション名。 @OptionName は varchar(35)であり、次のいずれかの値を指定できます。

[ @OptionValue = ] 'OptionValue'

@OptionName設定を有効 (TRUE、ON、yes、または1) または無効 (FALSE、OFF、no、または0) にするかどうかを指定します。 @OptionValue は 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 テーブルに関連付けられているすべてのインデックスに対して行ロックを禁止します。 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)