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, yes 또는 1) 또는 사용 안 함(FALSE, OFF, 아니요 또는 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. 특정 인덱스 옵션 설정

다음 예제에서는 테이블의 인덱스에 대한 페이지 잠금을 IX_Customer_TerritoryIDCustomer 허용하지 않습니다.

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  

참고 항목

NDEXPROPERTY(Transact-SQL)
시스템 저장 프로시저(Transact-SQL)
sys.indexes(Transact-SQL)