다음을 통해 공유

sp_indexoption(Transact-SQL)

  • 아티클

사용자 정의 클러스터형 및 비클러스터형 인덱스 또는 클러스터형 인덱스가 없는 테이블에 대한 잠금 옵션 값을 설정합니다.

SQL Server 데이터베이스 엔진은 자동으로 페이지, 행 또는 테이블 수준의 잠금을 선택합니다. 이러한 옵션을 수동으로 설정할 필요는 없습니다. 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인 경우 인덱스에 액세스할 때 행 잠금이 허용됩니다. 데이터베이스 엔진이 행 잠금 사용을 결정합니다. FALSE로 설정된 경우 행 잠금을 사용하지 않습니다. 기본값은 TRUE입니다.

    AllowPageLocks

    TRUE인 경우 인덱스에 액세스할 때 페이지 잠금이 허용됩니다. 페이지 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다. FALSE로 설정된 경우 페이지 잠금을 사용하지 않습니다. 기본값은 TRUE입니다.

    DisAllowRowLocks

    TRUE로 설정된 경우 행 잠금을 사용하지 않습니다. FALSE인 경우 인덱스에 액세스할 때 행 잠금이 허용됩니다. 데이터베이스 엔진이 행 잠금 사용을 결정합니다.

    DisAllowPageLocks

    TRUE로 설정된 경우 페이지 잠금을 사용하지 않습니다. FALSE인 경우 인덱스에 액세스할 때 페이지 잠금이 허용됩니다. 페이지 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.

  • [ @OptionValue =] 'value'
    option_name 설정을 사용(TRUE, ON, yes 또는 1)할지 또는 해제(FALSE, OFF, no 또는 0)할지를 지정합니다. value는 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 권한이 필요합니다.

예

1.특정 인덱스에 대한 옵션 설정

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

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

2.테이블의 모든 인덱스에 대한 옵션 설정

다음 예에서는 Product 테이블에 연결된 모든 인덱스에 대해 행 잠금을 허용하지 않습니다. 문의 결과를 표시하기 위해 sp_indexoption 프로시저를 실행하기 전과 후에 sys.indexes 카탈로그 뷰를 쿼리합니다.

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

3.클러스터형 인덱스가 없는 테이블에 대한 옵션 설정

다음 예에서는 클러스터형 인덱스가 없는 테이블(힙)에 대해 페이지 잠금을 허용하지 않습니다. 문의 결과를 표시하기 위해 sp_indexoption 프로시저를 실행하기 전과 후에 sys.indexes 카탈로그 뷰를 쿼리합니다.

USE AdventureWorks2012;
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)