다음을 통해 공유

sp_indexoption(Transact-SQL)

  • 아티클

적용 대상: SQL Server

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

SQL Server 데이터베이스 엔진 자동으로 페이지, 행 또는 테이블 수준 잠금을 선택합니다. 이러한 옵션을 수동으로 설정할 필요가 없습니다. sp_indexoption 는 특정 유형의 잠금이 항상 적절하다는 것을 확실하게 알고 있는 전문가를 위해 제공됩니다.

Important

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)이며 다음 값 중 하나일 수 있습니다.

값 설명
AllowRowLocks 인 TRUE덱스 액세스 시 행 잠금이 허용됩니다. 행 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다. 행 잠금이 사용되지 않는 경우 FALSE 기본값은 TRUE입니다.
AllowPageLocks 인 TRUE덱스 액세스 시 페이지 잠금이 허용됩니다. 페이지 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다. 페이지 잠금이 사용되지 않는 경우 FALSE 기본값은 TRUE입니다.
DisAllowRowLocks 행 잠금이 사용되지 않는 경우 TRUE 인 FALSE덱스 액세스 시 행 잠금이 허용됩니다. 행 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.
DisAllowPageLocks 페이지 잠금이 사용되지 않는 경우 TRUE 인 FALSE덱스 액세스 시 페이지 잠금이 허용됩니다. 페이지 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.

[ @OptionValue = ] 'OptionValue'

@OptionName 설정을 사용할지(, ON, 또는1yes) 사용 안 함(FALSETRUE, OFF또는 0no)으로 설정할지 여부를 지정합니다. @OptionValue 기본값이 없는 varchar(12)입니다.

반환 코드 값

0(성공) 또는 > 0(실패).

설명

XML 인덱스는 지원되지 않습니다. XML 인덱스가 지정된 경우, 그리고 테이블 이름이 인덱스 이름 없이 지정되고 테이블에 XML 인덱스가 있는 경우 문이 실패합니다. 이러한 옵션을 설정하려면 대신 ALTER INDEX를 사용합니다.

현재 행 및 페이지 잠금 속성을 표시하려면 INDEXPROPERTY 또는 sys.indexes 카탈로그 뷰를 사용합니다.

  • 행 수준, 페이지 수준 및 테이블 수준 잠금은 인덱스 언제AllowRowLocks = TRUE, 또는 DisAllowRowLocks = FALSEAllowPageLocks = TRUE DisAllowPageLocks = FALSE에 액세스할 때 허용됩니다. 데이터베이스 엔진은 적절한 잠금을 선택하고 행 또는 페이지 잠금에서 테이블 잠금으로 잠금을 에스컬레이션할 수 있습니다.

인덱스 액세스 시 AllowRowLocks = FALSE DisAllowRowLocks = TRUE AllowPageLocks = FALSE DisAllowPageLocks = TRUE또는 또는 인덱스 액세스 시 테이블 수준 잠금만 허용됩니다.

인덱스 없이 테이블 이름을 지정하면 해당 테이블의 모든 인덱스에 설정이 적용됩니다. 기본 테이블에 클러스터형 인덱스가 없는 경우(즉, 힙) 다음과 같이 설정이 적용됩니다.

  • AllowRowLocks DisAllowRowLocks 설정이 힙 및 연결된 비클러스터형 인덱스에 적용되거나 설정 TRUE FALSE되면 적용됩니다.

  • 옵션을 설정 TRUE 하거나 DisAllowPageLocks 설정FALSE하면 AllowPageLocks 설정이 힙 및 연결된 비클러스터형 인덱스에 적용됩니다.

  • AllowPageLocks 옵션을 설정 FALSE 하거나 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

관련 콘텐츠