다음을 통해 공유

ALTER TABLE index_option(Transact-SQL)

  • 아티클

적용 대상: Microsoft Fabric의 SQL ServerAzure SQL Database Azure SQL Managed Instance SQL 데이터베이스

ALTER TABLE을 사용하여 만든 제약 조건 정의의 일부인 색인에 적용할 수 있는 옵션 집합을 지정합니다.

인덱스 옵션에 대한 전체 설명은 CREATE INDEX를 참조하세요.

Transact-SQL 구문 표기 규칙

구문

{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
  | SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | XML_COMPRESSION = { ON | OFF }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> }
      [ , ...n ] ) ]
  | ONLINE = { ON | OFF }
  | RESUMABLE = { ON | OFF }
  | MAX_DURATION = <time> [ MINUTES ]
}

<range> ::=
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild__option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                           ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

인수

PAD_INDEX = { ON | OFF }

적용 대상: SQL Server 2008(10.0.x) 이상 버전

인덱스 패딩을 지정합니다. 기본값은 OFF입니다.

  • ON

    FILLFACTOR로 지정된 사용 가능한 공간의 비율이 인덱스의 중간 수준 페이지에 적용됩니다.

  • OFF 또는 fillfactor를 지정하지 않음

    중간 수준 페이지는 중간 페이지의 키 집합이 지정된 경우 최소한 인덱스에 사용할 수 있는 최대 크기의 행 하나를 위한 공간을 남겨 두고 거의 채워집니다.

FILLFACTOR = fillfactor

적용 대상: SQL Server 2008(10.0.x) 이상 버전

인덱스를 만들거나 변경할 때 데이터베이스 엔진이 각 인덱스 페이지의 리프 수준을 채우는 비율을 지정합니다. 지정한 값은 1에서 100까지의 정수 값이어야 합니다. 기본값은 0입니다.

참고

채우기 비율 값 0과 100은 모든 면에서 동일합니다.

IGNORE_DUP_KEY = { ON | OFF }

삽입 작업에서 고유 인덱스에 중복된 키 값을 삽입하려고 할 때 응답 유형을 지정합니다. IGNORE_DUP_KEY 옵션은 인덱스를 만들거나 다시 빌드한 후의 삽입 작업에만 적용됩니다. CREATE INDEX, ALTER INDEX 또는 UPDATE를 실행하는 경우에는 이 옵션이 아무런 영향을 미치지 않습니다. 기본값은 OFF입니다.

  • ON

    중복된 키 값이 고유 인덱스에 삽입되는 경우 경고 메시지가 나타나고 고유성 제약 조건을 위반하는 행만 실패합니다.

  • OFF

    중복된 키 값이 고유 인덱스에 삽입되는 경우 오류 메시지가 나타나고 전체 INSERT 작업이 롤백됩니다.

IGNORE_DUP_KEY 뷰에서 만든 인덱스, 특수하지 않은 인덱스, XML 인덱스, 공간 인덱스 및 필터링된 인덱스에 대해 설정할 ON 수 없습니다.

IGNORE_DUP_KEY를 보려면 sys.indexes를 사용합니다.

이전 버전과 호환되는 구문에서 WITH IGNORE_DUP_KEY는 WITH IGNORE_DUP_KEY = ON과 동일합니다.

STATISTICS_NORECOMPUTE = { ON | OFF }

지정된 인덱스와 관련된 통계에 대해 자동 통계 업데이트 옵션을 AUTO_STATISTICS_UPDATE사용하지 않도록 설정하거나 사용하도록 설정합니다. 기본값은 OFF입니다.

  • ON

    인덱스를 다시 작성한 후 자동 통계 업데이트를 사용할 수 없습니다.

  • OFF

    자동 통계 업데이트는 인덱스를 다시 빌드한 후에 사용하도록 설정됩니다.

자동 통계 업데이트를 복원하려면 절 없이 STATISTICS_NORECOMPUTE />로 OFF설정하거나 실행 UPDATE STATISTICS 합니다 NORECOMPUTE .

Warning

통계 자동 업데이트를 사용하지 않도록 설정하면 쿼리 최적화 프로그램에서 테이블과 관련된 쿼리에 대한 최적의 실행 계획을 선택하지 못할 수 있습니다. 이 옵션은 정규화된 데이터베이스 관리자만 사용해야 합니다.

이 설정은 다시 빌드 작업 중에 인덱스 관련 통계의 fullscan을 사용하여 자동 업데이트를 방지하지 않습니다.

ALLOW_ROW_LOCKS = { ON | OFF }

적용 대상: SQL Server 2008(10.0.x) 이상 버전

행 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

  • 켜기

    인덱스에 액세스할 때 행 잠금이 허용됩니다. 행 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.

  • OFF

    행 잠금은 사용되지 않습니다.

ALLOW_PAGE_LOCKS = { ON | OFF }

적용 대상: SQL Server 2008(10.0.x) 이상 버전

페이지 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

  • 켜기

    인덱스에 액세스할 때 페이지 잠금이 허용됩니다. 페이지 잠금을 사용하는 시점은 데이터베이스 엔진이 결정합니다.

  • OFF

    페이지 잠금은 사용되지 않습니다.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

적용 대상: SQL Server 2019(15.x) 이상 버전

마지막 페이지 삽입 경합에 최적화할지 여부를 지정합니다. 기본값은 OFF입니다. 자세한 내용은 문서의 순차 키 섹션을 CREATE INDEX 참조하세요.

SORT_IN_TEMPDB = { ON | OFF }

적용 대상: SQL Server 2008(10.0.x) 이상 버전

정렬 결과를 저장할지 여부를 지정합니다 tempdb. 기본값은 OFF입니다.

  • ON

    인덱스 작성에 사용되는 중간 정렬 결과는 에 저장 tempdb됩니다. 이렇게 하면 사용자 데이터베이스와 다른 디스크 집합에 있는 경우 tempdb 인덱스를 만드는 데 필요한 시간이 줄어들 수 있습니다. 그러나 인덱스 작성 중에 사용되는 디스크 공간의 크기는 커집니다.

  • OFF

    중간 정렬 결과가 인덱스와 같은 데이터베이스에 저장됩니다.

ONLINE = { ON | OFF }

적용 대상: SQL Server 2008(10.0.x) 이상 버전

인덱스 작업 중 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다. REBUILD 는 작업으로 ONLINE 수행될 수 있습니다.

참고 항목

고유한 비클러스터형 인덱스는 온라인으로 만들 수 없습니다. 여기에는 제약 조건 또는 PRIMARY KEY 제약 조건으로 인해 UNIQUE 생성된 인덱스가 포함됩니다.

  • ON

    인덱스 작업 중에 장기 테이블 잠금이 유지되지 않습니다. 인덱스 작업의 주 단계 중 내재된 공유(IS) 잠금만 원본 테이블에 유지됩니다. 따라서 기본 테이블 및 인덱스에 대한 쿼리나 업데이트를 처리할 수 있습니다. 작업을 시작할 때 짧은 시간 동안 원본 개체에서 공유(S) 잠금이 유지됩니다. 작업이 끝나면 비클러스터형 인덱스가 생성되는 경우 짧은 기간 동안 원본에서 S(공유) 잠금을 획득합니다. 또는 Sch-M(스키마 수정) 잠금은 클러스터형 인덱스를 만들거나 온라인으로 삭제하고 클러스터형 또는 비클러스터형 인덱스를 다시 작성할 때 획득됩니다. 온라인 인덱스 잠금은 짧은 메타데이터 잠금이지만 특히 Sch-M 잠금은 이 테이블에서 모든 차단 트랜잭션이 완료될 때까지 기다려야 합니다. 대기 시간 동안 Sch-M 잠금은 동일 테이블에 액세스할 때 이 잠금 뒤에서 기다리는 다른 모든 트랜잭션을 차단합니다. ONLINE 는 인덱스가 로컬 임시 테이블에 생성되는 시점으로 설정할 ON 수 없습니다.

    참고 항목

    온라인 인덱스 다시 작성은 이 섹션의 뒷부분에서 설명하는 low_priority_lock_wait 옵션을 설정할 수 있습니다. low_priority_lock_wait는 온라인 인덱스 다시 작성 중에 S 및 Sch-M 잠금 우선 순위를 관리합니다.

  • OFF

    인덱스 작업 중에 테이블 잠금이 적용됩니다. 이렇게 하면 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 클러스터형 인덱스를 생성, 다시 작성 또는 삭제하거나 비클러스터형 인덱스를 다시 작성 또는 삭제하는 오프라인 인덱스 작업을 통해 테이블의 SCH-M(스키마 수정) 잠금을 획득합니다. 이렇게 하면 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업을 통해 테이블의 S(공유) 잠금을 획득합니다. 이렇게 하면 기본 테이블에 대한 업데이트를 방지하지만 문과 같은 SELECT 읽기 작업을 허용합니다.

자세한 내용은 온라인 인덱스 작업의 작동 원리를 참조하세요.

참고 항목

온라인 인덱스 작업은 일부 Microsoft SQL Server 버전에서 사용할 수 없습니다. SQL Server 버전에서 지원되는 기능 목록은 SQL Server 2022의 버전과 지원하는 기능을 참조하세요.

RESUMABLE = { ON | OFF}

적용 대상: SQL Server 2022(16.x) 이상 버전

ALTER TABLE ADD CONSTRAINT 작업이 다시 시작될 수 있는지 여부를 지정합니다. 테이블 제약 조건 추가 작업은 ON일 때 다시 시작할 수 있습니다. 테이블 제약 조건 추가 작업은 다음과 같은 경우 OFF다시 시작되지 않습니다. 기본값은 OFF입니다. RESUMABLE 옵션이 ON으로 설정되면 ONLINE = ON 옵션이 필요합니다.

MAX_DURATION(requiresONLINE = ON)와 함께 RESUMABLE = ON 사용하면 일시 중지되기 전에 다시 시작 가능한 온라인 추가 제약 조건 작업이 실행되는 시간(분 단위로 지정된 정수 값)을 나타냅니다. 지정하지 않으면 작업이 완료될 때까지 계속됩니다. MAXDOP 은 지원 RESUMABLE = ON 됩니다.

다시 시작할 수 있는 ALTER TABLE ADD CONSTRAINT 작업을 사용하도록 설정하고 사용하는 방법에 대한 자세한 내용은 다시 시작할 수 있는 추가 테이블 제약 조건을 참조하세요.

MAXDOP = max_degree_of_parallelism

적용 대상: SQL Server 2008(10.0.x) 이상 버전

인덱스 작업 동안 max degree of parallelism 구성 옵션을 재정의합니다. 자세한 내용은 최대 병렬 처리 수준 구성(서버 구성 옵션)을 참조하세요. 병렬 계획 실행에 사용되는 프로세서 수를 제한하는 데 사용합니다 MAXDOP . 최대값은 64개입니다.

max_degree_of_parallelism은 다음 중 하나일 수 있습니다.

  • 1: 병렬 계획 생성을 표시하지 않습니다.
  • >1: 병렬 인덱스 작업에 사용되는 프로세서의 최대 수를 지정된 수로 제한합니다.
  • 0 (기본값): 현재 시스템 워크로드에 따라 실제 프로세서 수 이하를 사용합니다.

자세한 내용은 병렬 인덱스 작업 구성을 참조하세요.

참고

병렬 인덱스 작업은 일부 Microsoft SQL Server 버전에서 사용할 수 있습니다. SQL Server 버전에서 지원되는 기능 목록은 SQL Server 2022의 버전과 지원하는 기능을 참조하세요.

DATA_COMPRESSION

적용 대상: SQL Server 2008(10.0.x) 이상 버전

지정된 테이블, 파티션 번호 또는 파티션 범위에 대한 데이터 압축 옵션을 지정합니다. 옵션은 다음과 같습니다.

  • 없음

    테이블 또는 지정된 파티션이 압축되지 않습니다. rowstore 테이블에만 적용되며 columnstore 테이블에는 적용되지 않습니다.

  • ROW

    테이블 또는 지정된 파티션이 행 압축을 사용하여 압축됩니다. rowstore 테이블에만 적용되며 columnstore 테이블에는 적용되지 않습니다.

  • PAGE

    테이블 또는 지정된 파티션이 페이지 압축을 사용하여 압축됩니다. rowstore 테이블에만 적용되며 columnstore 테이블에는 적용되지 않습니다.

  • COLUMNSTORE

    적용 대상: SQL Server 2014(12.x) 이상

    columnstore 테이블에만 적용됩니다. COLUMNSTORE 는 옵션으로 COLUMNSTORE_ARCHIVE 압축된 파티션의 압축을 풉니다. 데이터가 복원되면 COLUMNSTORE 모든 columnstore 테이블에 사용되는 columnstore 압축을 사용하여 인덱스가 계속 압축됩니다.

  • COLUMNSTORE_ARCHIVE

    적용 대상: SQL Server 2014(12.x) 이상

    클러스터형 columnstore 인덱스로 저장된 테이블인 columnstore 테이블에만 적용됩니다. COLUMNSTORE_ARCHIVE 는 지정된 파티션을 더 작은 크기로 압축합니다. 보관하거나 보다 적은 스토리지가 필요한 기타 상황에서 사용할 수 있으며 저장 및 검색에 더 많은 시간을 이용할 수 있습니다.

압축에 대한 자세한 내용은 데이터 압축을 참조하세요.

XML_COMPRESSION

적용 대상: SQL Server 2022(16.x) 이상 버전, Azure SQL 데이터베이스, Azure SQL Managed Instance.

테이블의 모든 xml 데이터 형식 열에 대한 XML 압축 옵션을 지정합니다. 옵션은 다음과 같습니다.

  • 켜기

    xml 데이터 형식을 사용하는 열이 압축됩니다.

  • OFF

    xml 데이터 형식을 사용하는 열이 압축되지 않습니다.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

적용 대상: SQL Server 2008(10.0.x) 이상 버전

DATA_COMPRESSION 또는 XML_COMPRESSION 설정이 적용되는 파티션을 지정합니다. 테이블이 분할되지 않으면 인수에서 ON PARTITIONS 오류가 발생합니다. 절이 ON PARTITIONS 제공되지 않으면 분할된 테이블의 DATA_COMPRESSION 모든 파티션에 해당 또는 XML_COMPRESSION 옵션이 적용됩니다.

<partition_number_expression>은 다음과 같은 방법으로 지정할 수 있습니다.

  • 파티션 번호(예: ON PARTITIONS (2).
  • 여러 개별 파티션의 파티션 번호를 쉼표로 구분하여 지정합니다(예: ON PARTITIONS (1, 5)).
  • 범위와 개별 파티션을 모두 지정합니다(예: ON PARTITIONS (2, 4, 6 TO 8)).

<range>는 TO라는 단어로 구분된 파티션 번호로 지정할 수 있습니다(예: ON PARTITIONS (6 TO 8)).

여러 파티션에 대해 서로 다른 데이터 압축 유형을 설정하려면 DATA_COMPRESSION 옵션을 두 번 이상 지정합니다. 예를 들면 다음과 같습니다.

--For rowstore tables
REBUILD WITH
(
  DATA_COMPRESSION = NONE ON PARTITIONS (1),
  DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
  DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

대부분의 경우 인덱스를 다시 빌드하면 분할된 인덱스의 모든 파티션도 다시 빌드됩니다. 다음 옵션을 단일 파티션에 적용하는 경우 일부 파티션이 다시 빌드되지 않습니다.

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait

적용 대상: SQL Server 2014(12.x) 이상

A SWITCH 또는 온라인 인덱스 다시 작성은 이 테이블에 대한 차단 작업이 없는 즉시 완료됩니다. WAIT_AT_LOW_PRIORITY 또는 온라인 인덱스 SWITCH 다시 작성 작업을 즉시 완료할 수 없는 경우 대기 중임을 나타냅니다. 이 작업은 우선 순위가 낮은 잠금을 보류하여, DDL 문과 충돌하는 잠금을 가진 다른 작업이 계속 수행될 수 있도록 허용합니다. WAIT AT LOW PRIORITY 옵션을 생략하면 WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)와 동일합니다.

MAX_DURATION = time [ MINUTES ]

획득해야 하는 SWITCH 온라인 인덱스 다시 작성 잠금의 대기 시간(분 단위로 지정된 정수 값)은 DDL 명령을 실행할 때 대기합니다. 온라인 인 SWITCH 덱스 다시 작성 작업은 즉시 완료하려고 시도합니다. 작업이 시간 동안 MAX_DURATION 차단되면 작업 중 ABORT_AFTER_WAIT 하나가 실행됩니다. MAX_DURATION 시간은 항상 분 단위이며 단어를 MINUTES 생략할 수 있습니다.

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

  • NONE

    SWITCH 잠금 우선 순위를 변경하지 않고(일반 우선 순위 사용) 온라인 인덱스 다시 작성 작업을 계속합니다.

  • SELF

    아무 작업도 수행하지 않고 현재 실행 중인 온라인 인덱스 SWITCH 다시 작성 DDL 작업을 종료합니다.

  • BLOCKERS

    작업을 계속할 수 있도록 현재 SWITCH 또는 온라인 인덱스 다시 작성 DDL 작업을 차단하는 모든 사용자 트랜잭션을 종료합니다.

    BLOCKERS 에는 ALTER ANY CONNECTION 사용 권한이 필요합니다.

관련 콘텐츠