CREATE INDEX(Transact-SQL)

아티클
2025-04-04

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

테이블 또는 뷰에 관계 인덱스를 만듭니다. 이 인덱스는 클러스터형 또는 비클러스터형 B-트리 인덱스이므로 rowstore 인덱스라고도 합니다. 테이블에 데이터가 채워지기 전에 rowstore 인덱스를 만들 수 있습니다. 특히 쿼리가 특정 열에서 값을 선택하거나 값을 특정 순서로 정렬해야 하는 경우 rowstore 인덱스를 사용하여 쿼리 성능을 개선할 수 있습니다.

참고 항목

설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 데이터베이스 엔진은 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 최적화 테이블 인덱스에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

Azure Synapse Analytics 및 Analytics Platform System(PDW)은 현재 Unique 제약 조건을 지원하지 않습니다. 고유 제약 조건을 참조하는 예제는 SQL Server, Azure SQL Database 및 Azure SQL Managed Instance에만 적용됩니다.

인덱스 디자인 지침에 대한 자세한 내용은 SQL Server 인덱스 디자인 가이드를 참조하세요.

예:

테이블 또는 뷰에 비클러스터형 인덱스 만들기
```
CREATE INDEX index1 ON schema1.table1 (column1);
```
테이블에 클러스터형 인덱스 만들기 및 테이블에 3부로 구성된 이름 사용
```
CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
```
Unique 제약 조건을 사용하여 비클러스터형 인덱스 만들기 및 정렬 순서 지정
```
CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
```

주요 시나리오:

SQL Server 2016(13.x),Azure SQL Database 및 Azure SQL Managed Instance부터 columnstore 인덱스에서 비클러스터형 인덱스로 데이터 웨어하우징 쿼리 성능을 향상시킬 수 있습니다. 자세한 내용은 Columnstore 인덱스 - 데이터 웨어하우스를 참조하세요.

추가 인덱스 형식은 다음을 참조하세요.

Transact-SQL 구문 표기 규칙

Syntax

SQL Server, Azure SQL Database 및 Azure SQL Managed Instance용 구문

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

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

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

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

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

이전 버전과 호환되는 관계형 인덱스

중요

이전 버전과 호환되는 관계형 인덱스 구문 구조는 나중 버전의 SQL Server에서 제거됩니다. 새 개발 작업에서는 이 구문 구조를 사용하지 않도록 하고 현재 이 기능을 사용하는 애플리케이션은 수정하십시오. 대신 <relational_index_option>에서 지정된 구문 구조를 사용합니다.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Azure Synapse Analytics 및 병렬 데이터 웨어하우스용 구문


CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

인수

UNIQUE

테이블 또는 뷰에 고유 인덱스를 만듭니다. 고유 인덱스는 두 개의 행에 동일한 인덱스 키 값을 가질 수 없습니다.

데이터베이스 엔진은 중복 값이 이미 포함된 열에 대해 설정 여부에 관계없이 고유한 인덱스를 만들 수 ON없습니다IGNORE_DUP_KEY. 이 작업이 시도되면 데이터베이스 엔진에 오류 메시지가 표시됩니다. 열에 고유 인덱스를 만들려면 먼저 중복 값을 제거해야 합니다.

제약 조건은 UNIQUE 값으로 처리됩니다 NULL . 열이 null 허용되고 UNIQUE 열에 제약 조건이 있는 경우 최대 하나의 행이 NULL 허용됩니다.

CLUSTERED

인덱스 키 열에 지정된 정렬 순서가 디스크의 인덱스 구조에서 페이지 순서를 결정하는 인덱스를 만듭니다. 클러스터형 인덱스의 아래쪽 또는 리프에 있는 페이지의 행에는 항상 테이블의 모든 열이 포함됩니다. 인덱스의 상위 수준에 있는 페이지의 행에는 키 열만 포함됩니다.

각 테이블마다 클러스터형 인덱스를 하나만 포함할 수 있습니다. 클러스터형 인덱스가 테이블에 있으면 테이블의 모든 데이터가 포함됩니다. 클러스터형 인덱스가 없는 테이블을 힙이라고 합니다.

고유 클러스터형 인덱스가 있는 뷰를 인덱싱된 뷰라고 합니다. 인덱싱된 뷰에는 클러스터형 인덱스가 하나만 있을 수 있습니다. 뷰에서 고유 클러스터형 인덱스를 만들면 물리적으로 뷰를 구체화합니다. 같은 뷰에 다른 인덱스를 정의하려면 먼저 고유 클러스터형 인덱스를 만들어야 합니다. 자세한 정보는 인덱싱된 뷰 만들기를 참조하세요.

비클러스터형 인덱스를 만들기 전에 항상 클러스터형 인덱스를 만듭니다. 테이블의 기존 비클러스터형 인덱스는 클러스터형 인덱스를 만들 때 다시 작성되며, 이는 테이블이 큰 경우 리소스를 많이 사용하는 작업입니다.

CLUSTERED를 지정하지 않으면 비클러스터형 인덱스가 만들어집니다.

참고 항목

클러스터형 인덱스에는 테이블의 모든 데이터가 포함되므로 클러스터형 인덱스를 만들고 or ON filegroup_name 절을 사용하면 ON partition_scheme_name 테이블이 만들어진 파일 그룹에서 새 파티션 구성표 또는 파일 그룹으로 테이블이 효과적으로 이동합니다. 특정 파일 그룹에서 테이블이나 인덱스를 만들기 전에 사용 가능한 파일 그룹과 인덱스를 만들 공간이 충분한지 확인합니다.

경우에 따라 클러스터형 인덱스를 만들면 이전에 사용하지 않도록 설정된 인덱스를 사용할 수 있습니다. 자세한 내용은 인덱스 및 제약 조건 사용 및 인덱 스 및 제약 조건 사용 안 함을 참조하세요.

NONCLUSTERED

인덱스 키 열에 지정된 정렬 순서가 디스크의 인덱스 구조에서 페이지 순서를 결정하는 인덱스를 만듭니다. 클러스터형 인덱스와 달리 비클러스터형 인덱스의 리프 수준에 있는 페이지의 행에는 인덱스 키 열만 포함됩니다. 필요에 따라 비키 열의 하위 집합은 절을 INCLUDE 사용하여 포함할 수 있습니다.

각 테이블에는 인덱스를 만드는 방법에 관계없이 최대 999개의 비클러스터형 인덱스가 있을 수 있습니다( 암시적으로 및 UNIQUE 제약 조건을 사용 PRIMARY KEY 하거나 명시적으로 사용CREATE INDEX).

인덱싱된 뷰의 경우 비클러스터형 인덱스는 이미 고유 클러스터형 인덱스가 정의되어 있는 뷰에서만 만들 수 있습니다.

달리 지정하지 않으면 기본 인덱스 유형은 비클러스터형입니다.

index_name

인덱스의 이름입니다. 인덱스 이름은 테이블이나 뷰에서 고유해야 하지만 데이터베이스 내에서 고유할 필요는 없습니다. 인덱스 이름은 식별자 규칙을 따라야 합니다.

column

인덱스의 기준이 되는 열입니다. 지정된 열에 있는 결합된 값에 복합 인덱스를 만들려면 두 개 이상의 열 이름을 지정합니다. 복합 인덱스에 포함할 열을 table_or_view_name 다음의 괄호 안에 정렬 우선 순위 순서대로 나열합니다.

단일 복합 인덱스 키에 최대 32개의 열을 결합할 수 있으며 복합 인덱스 키의 모든 열은 동일한 테이블 또는 뷰에 있어야 합니다. 결합된 인덱스 값의 최대 허용 크기는 클러스터형 인덱스의 경우 900바이트, 비클러스터형 인덱스의 경우 1,700바이트입니다. 한도는 SQL Database 및 SQL Server 2016(13.x) 이전 버전의 경우 16열 및 900바이트입니다.

큰 개체(LOB) 데이터 형식 ntext, text, varchar(max), nvarchar(max), varbinary(max), xml 또는 image인 열은 인덱스의 키 열로 지정할 수 없습니다. 또한 인덱싱된 뷰 정의는 문에서 CREATE INDEX 참조되지 않더라도 ntext, text 또는 이미지 열을 포함할 수 없습니다.

이진 순서를 지원하는 CLR 사용자 정의 형식 열에 인덱스를 만들 수 있습니다. 메서드가 결정적으로 표시되고 데이터 액세스 작업을 수행하지 않는 동안 사용자 정의 형식 열의 메서드 호출로 정의된 계산 열에 인덱스를 만들 수도 있습니다. CLR 사용자 정의 형식 열을 인덱싱하는 방법에 대한 자세한 내용은 CLR 사용자 정의 형식을 참조하세요.

[ ASC | DESC ]

특정 인덱스 열의 정렬 방향을 오름차순 또는 내림차순으로 지정합니다. 기본값은 ASC입니다.

INCLUDE (column [ ,... n ] )

비클러스터형 인덱스의 리프 수준에 추가할 키가 아닌 열을 지정합니다. 비클러스터형 인덱스는 고유하거나 고유하지 않을 수 있습니다.

열 이름은 목록에서 반복 INCLUDE 할 수 없으며 키 열과 키가 아닌 열로 동시에 사용할 수 없습니다. 클러스터형 인덱스가 테이블에 정의된 경우 비클러스터형 인덱스에는 항상 클러스터형 인덱스 열이 암시적으로 포함됩니다. 자세한 내용은 포함된 열을 사용하여 인덱스 만들기를 참조하세요.

text, ntext및 image를 제외한 모든 데이터 형식을 사용할 수 있습니다. SQL Server 2012(11.x),Azure SQL Database 및 Azure SQL Managed Instance에서 지정된 키가 아닌 열 중 하나가 varchar(max), nvarchar(max) 또는 varbinary(max) 데이터 형식인 경우 이 옵션을 사용하여 ONLINE 인덱스를 작성하거나 다시 작성할 수 있습니다.

결정적이면서 정확하거나 정확하지 않은 계산 열은 포괄 열이 될 수 있습니다. 계산 열 데이터 형식이 포함된 열로 허용되는 한 이미지, ntext, text, varchar(max), nvarchar(max), varbinary(max) 및 xml 데이터 형식에서 파생된 계산 열을 포함할 수 있습니다. 자세한 내용은 계산된 열의 인덱스를 참조하세요.

XML 인덱스 만들기에 대한 자세한 내용은 CREATE XML INDEX를 참조하세요.

WHERE <filter_predicate>

인덱스에 포함할 행을 지정하여 필터링된 인덱스를 만듭니다. 필터링된 인덱스는 테이블의 비클러스터형 인덱스여야 합니다. 필터링된 인덱스의 데이터 행에 대한 필터링된 통계를 만듭니다.

필터 조건자는 간단한 비교 논리를 사용하며 계산 열, UDT(사용자 정의 데이터 형식) 열, 공간 데이터 형식 열 또는 hierarchyid 데이터 형식 열을 참조할 수 없습니다. 비교 연산자를 사용한 리터럴과의 NULL 비교는 허용되지 않습니다. 대신 IS NULL 및 IS NOT NULL 연산자를 사용합니다.

다음은 Production.BillOfMaterials 테이블에 대한 필터 조건자의 몇 가지 예입니다.

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

필터링된 인덱스는 XML 인덱스 및 전체 텍스트 인덱스에는 적용되지 않습니다. 인덱스의 경우 UNIQUE 선택한 행에만 고유한 인덱스 값이 있어야 합니다. 필터링된 인덱스에는 IGNORE_DUP_KEY 옵션을 사용할 수 없습니다.

ON partition_scheme_name ( column_name )

분할된 인덱스의 파티션이 매핑되는 파일 그룹을 정의하는 파티션 구성표를 지정합니다. 파티션 구성표는 CREATE PARTITION SCHEME 또는 ALTER PARTITION SCHEME의 실행을 통해 데이터베이스 내에 있어야 합니다. column_name 인덱스의 분할 열을 지정합니다. 이 열은 partition_scheme_name에서 사용하는 파티션 함수의 인수와 데이터 형식, 길이 및 전체 자릿수가 일치해야 합니다. column_name은 인덱스 정의의 열만 사용할 필요는 없으며 고유 인덱스 분할 시를 제외하고 기본 테이블의 모든 열을 지정할 수 column_name 고유 키로 사용되는 열 중에서 선택해야 합니다. 이 제한 사항으로 인해 데이터베이스 엔진은 단일 파티션 내에서만 키 값의 고유성을 확인할 수 있습니다.

참고 항목

비고유 클러스터형 인덱스를 분할하는 경우 데이터베이스 엔진은 기본적으로 지정되지 않은 분할 열을 클러스터형 인덱스 키 목록에 추가합니다. 비고유 비클러스터형 인덱스를 분할하는 경우 데이터베이스 엔진은 지정되지 않은 분할 열을 인덱스의 키가 아닌 포괄 열로 추가합니다.

partition_scheme_name 또는 filegroup이 지정되지 않고 테이블이 분할된 경우 인덱스는 동일한 분할 열을 사용하여 동일한 파티션 구성표에 기본 테이블로 배치됩니다.

참고 항목

XML 인덱스에서 파티션 구성표를 지정할 수 없습니다. 기본 테이블이 분할되면 XML 인덱스는 테이블과 동일한 파티션 구성표를 사용합니다.

인덱스, 분할된 테이블 및 인덱스를 분할하는 방법에 대한 자세한 내용을 보려면

ON filegroup_name

주어진 파일 그룹에 지정된 인덱스를 만듭니다. 지정된 위치가 없고 테이블 또는 뷰가 분할되지 않은 경우 인덱스는 동일한 파일 그룹을 기본 테이블 또는 뷰로 사용합니다. 파일 그룹은 이미 존재해야 합니다.

ON [기본값]

테이블 또는 보기와 동일한 파일 그룹 또는 파티션 구성표에 지정된 인덱스를 만듭니다.

이 컨텍스트에서 용어 default는 키워드가 아닙니다. 테이블 또는 뷰의 파일 그룹 또는 분할된 구성표에 대한 식별자이며, 파일 그룹 또는 ON [default]뷰와 ON "default" 같이 구분되어야 합니다. 지정한 QUOTED_IDENTIFIER 경우 "default" 현재 세션에 대한 옵션이어야 ON 합니다. 이 값은 기본 설정입니다. 자세한 내용은 SET QUOTED_IDENTIFIER를 참조하세요.

참고 항목

의 컨텍스트 CREATE INDEX"default"[default] 에서 데이터베이스 기본 파일 그룹을 나타내지 않습니다. 기본 테이블 또는 뷰에서 사용하는 파일 그룹 또는 파티션 구성표를 나타냅니다. 이는 데이터베이스 기본 파일 그룹에 테이블을 배치하는 위치 "default" 와 [default] 다릅니다CREATE TABLE.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

클러스터형 인덱스를 만들 때 테이블에 대한 FILESTREAM 데이터의 위치를 지정합니다. FILESTREAM_ON 절을 사용하여 FILESTREAM 데이터를 다른 FILESTREAM 파일 그룹이나 파티션 구성표로 이동할 수 있습니다.

filestream_filegroup_name FILESTREAM 파일 그룹의 이름입니다. 파일 그룹에는 CREATE DATABASE 또는 ALTER DATABASE 문을 사용하여 파일 그룹에 대해 정의된 파일이 하나 포함되어야 하며, 그렇지 않으면 오류가 발생합니다.

테이블이 분할된 경우에는 FILESTREAM_ON 절이 포함되어야 하며 이 절에서 테이블의 파티션 구성표와 동일한 파티션 함수 및 파티션 열을 사용하는 FILESTREAM 파일 그룹의 파티션 구성표를 지정해야 합니다. 그렇지 않으면 오류가 발생합니다.

테이블이 분할되지 않은 경우에는 FILESTREAM 열을 분할할 수 없습니다. 테이블의 FILESTREAM 데이터는 FILESTREAM_ON 절에 지정된 단일 파일 그룹에 저장되어야 합니다.

클러스터형 인덱스가 만들어지고 테이블에 FILESTREAM 열이 포함되어 있지 않은 경우 FILESTREAM_ON NULL에 CREATE INDEX을 지정할 수 있습니다.

자세한 내용은 FILESTREAM(SQL Server)을 참조하세요.

<object>::=

인덱스할 정규화되거나 정규화되지 않은 개체입니다.

database_name

데이터베이스의 이름입니다.

schema_name

테이블이나 뷰가 속한 스키마의 이름입니다.

table_or_view_name

인덱싱할 테이블 또는 뷰의 이름입니다.

뷰에서 인덱스 만들기를 위해 뷰는 .로 정의 SCHEMABINDING해야 합니다. 뷰에 비클러스터형 인덱스를 만들려면 먼저 고유 클러스터형 인덱스를 만들어야 합니다. 인덱싱된 뷰에 대한 자세한 내용은 비고를 참조하세요.

SQL Server 2016(13.x)부터 개체는 클러스터형 columnstore 인덱스와 함께 저장된 테이블일 수 있습니다.

Azure SQL Database는 <database_name>.<schema_name>.<object_name> 현재 데이터베이스 이름이거나 <database_name><database_name>tempdb<object_name> 또는 #시작하는 경우 세 부분으로 구성된 이름 형식 ## 지원합니다. 스키마 이름이 dbo경우 <schema_name> 생략할 수 있습니다.

<relational_index_option>::=

인덱스를 만들 때 사용할 옵션을 지정합니다.

PAD_INDEX = { ON | OFF }

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

켜기

채우기 인수로 지정된 여유 공간의 백분율은 인덱스의 중간 수준 페이지에 적용됩니다. FILLFACTOR 동시에 지정되지 않은 경우 PAD_INDEXON설정되면 sys.indexes 채우기 비율 값이 사용됩니다.
OFF

중간 수준 페이지는 중간 페이지의 키 집합을 고려하며 인덱스가 가질 수 있는 최대 크기의 한 행에 필요한 공간을 충분히 남기고 용량을 거의 채웁니다. PAD_INDEX ON 설정되었지만 채우기 인수가 지정되지 않은 경우에도 발생합니다.

이 PAD_INDEX 옵션은 지정 PAD_INDEXFILLFACTOR한 경우에만 FILLFACTOR 유용합니다. 지정된 FILLFACTOR 백분율이 한 행을 허용할 만큼 충분히 크지 않은 경우 데이터베이스 엔진은 내부적으로 최소값을 허용하도록 백분율을 재정의합니다. 중간 인덱스 페이지의 행 수는 값 FILLFACTOR이 얼마나 낮은지에 관계없이 2보다 작지 않습니다.

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

FILLFACTOR = fillfactor

인덱스를 만들거나 다시 작성할 때 데이터베이스 엔진에서 각 인덱스 페이지의 리프 수준을 채우는 비율을 지정합니다. fillfactor 값은 1에서 100까지의 정수 값이어야 합니다. 채우기 비율 값 0과 100은 모든 면에서 동일합니다. fillfactor가 100이면 데이터베이스 엔진는 리프 페이지가 꽉 찬 인덱스를 만듭니다.

FILLFACTOR 설정은 인덱스를 만들거나 다시 빌드할 때만 적용됩니다. 데이터베이스 엔진에서는 페이지에 지정된 비율의 빈 공간을 동적으로 유지하지 않습니다.

채우기 비율 설정을 보려면 sys.indexes 카탈로그 뷰의 열을 사용합니다fill_factor.

중요

FILLFACTOR 100보다 작은 인덱스를 만들면 데이터베이스 엔진이 인덱스를 만들거나 다시 작성할 때 채우기 비율에 따라 데이터를 재배포하기 때문에 데이터가 차지하는 스토리지 공간의 양이 증가합니다.

자세한 내용은 인덱스의 채우기 비율 지정을 참조하세요.

SORT_IN_TEMPDB = { ON | OFF }

임시 정렬 결과를 tempdb저장할지 여부를 지정합니다. 기본값은 OFF Azure SQL Database 하이퍼스케일을 제외한 것입니다. 하이퍼스케일의 모든 인덱스 빌드 작업의 경우 다시 시작 가능한 인덱스 빌드를 사용하지 않는 한 SORT_IN_TEMPDB 항상 ON. 다시 시작하는 인덱스 빌드의 경우 SORT_IN_TEMPDB 항상 OFF.

켜기

인덱스 작성에 사용되는 중간 정렬 결과는 에 저장 tempdb됩니다. 이렇게 하면 인덱스 만들기에 필요한 시간이 단축될 수 있습니다. 그러나 인덱스 작성 중에 사용되는 디스크 공간의 크기는 커집니다.
OFF

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

사용자 데이터베이스에서 인덱 tempdb 스 만들기에 필요한 공간 외에도 중간 정렬 결과를 저장할 추가 공간이 거의 동일해야 합니다. 자세한 내용은 인덱스 대한SORT_IN_TEMPDB 옵션을 참조하세요.

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

IGNORE_DUP_KEY = { ON | OFF }

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

켜기

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

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

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

인덱스에 대한 IGNORE_DUP_KEY 설정을 보려면 ignore_dup_key 카탈로그 뷰의 열을 사용합니다.

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

STATISTICS_NORECOMPUTE = { ON | OFF}

통계를 다시 계산할지 여부를 지정합니다. 기본값은 OFF입니다.

켜기

이전 통계가 자동으로 다시 계산되지 않습니다.
OFF

자동 통계 업데이트가 설정됩니다.

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

경고

STATISTICS_NORECOMPUTE = ON설정하여 통계 자동 재계산을 사용하지 않도록 설정하면 쿼리 최적화 프로그램에서 테이블과 관련된 쿼리에 대한 최적의 실행 계획을 선택하지 못할 수 있습니다.

STATISTICS_NORECOMPUTE ON 설정해도 인덱스 다시 작성 작업 중에 발생하는 인덱스 통계 업데이트가 방지되지는 않습니다.

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

STATISTICS_INCREMENTAL = {ON | OFF}

적용: SQL Server 2014(12.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

생성 ON되는 통계는 파티션 통계당입니다. OFF통계 트리가 삭제되고 SQL Server가 통계를 다시 계산합니다. 기본값은 OFF입니다.

파티션별 통계가 지원되지 않는 경우에는 이 옵션이 무시되고 경고가 생성됩니다. 증분 통계는 다음 경우에 지원되지 않습니다.

기본 테이블을 기준으로 파티션 정렬되지 않은 인덱스를 사용하여 작성된 통계입니다.
Always On 읽기 가능한 보조 데이터베이스에 대해 작성된 통계입니다.
읽기 전용 데이터베이스에 대해 작성된 통계입니다.
필터링된 인덱스에 대해 작성된 통계입니다.
뷰에 대해 작성된 통계입니다.
내부 테이블에 대해 작성된 통계입니다.
공간 인덱스 또는 XML 인덱스를 사용하여 작성된 통계입니다.

DROP_EXISTING = { ON | OFF }

수정된 열 사양을 사용하여 기존 클러스터형 또는 비클러스터형 인덱스를 삭제하고 다시 만들며 인덱스 이름을 동일하게 유지하는 옵션입니다. 기본값은 OFF입니다.

켜기

기존 인덱스를 삭제하고 다시 만들도록 지정하며, 이름은 index_name 매개 변수와 같아야 합니다.
OFF

기존 인덱스를 삭제하고 다시 만들지 않도록 지정합니다. 지정된 인덱스 이름이 이미 존재하는 경우 SQL Server 오류가 표시됩니다.

DROP_EXISTING을 사용하여 다음과 같이 변경할 수 있습니다.

비클러스터형 rowstore 인덱스를 클러스터형 rowstore 인덱스로.

DROP_EXISTING를 사용하여 다음과 같이 변경할 수 없습니다.

클러스터형 rowstore 인덱스를 비클러스터형 rowstore 인덱스로.
클러스터형 columnstore 인덱스를 임의 형식의 rowstore 인덱스로.

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

ONLINE = { ON | OFF }

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

중요

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

켜기

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

참고 항목

WAIT_AT_LOW_PRIORITY 옵션을 사용하여 온라인 인덱스 작업 중 차단을 줄이거나 방지할 수 있습니다. 자세한 내용은 온라인 인덱스 작업 WAIT_AT_LOW_PRIORITY 참조하세요.
OFF

인덱스 작업 중에 테이블 잠금이 적용됩니다. 클러스터형, 공간 또는 XML 인덱스를 만들거나 다시 작성하거나 삭제하거나 비클러스터형 인덱스를 다시 작성하거나 삭제하는 오프라인 인덱스 작업은 테이블에 대한 스키마 수정(Sch-M) 잠금을 획득합니다. 이 경우 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업은 처음에 테이블에 대한 공유(S) 잠금을 획득합니다. 이렇게 하면 기본 테이블 정의가 수정되지 않지만 인덱스 빌드가 진행되는 동안 테이블의 데이터를 읽고 수정할 수 있습니다.

자세한 내용은 온라인 인덱스 작업 수행 및 온라인 인덱스 작업 대한지침을 참조하세요.

전역 임시 테이블의 인덱스를 비롯한 인덱스를 온라인으로 만들 수 있습니다. 단, 다음 사례는 예외입니다.

XML 인덱스
로컬 임시 테이블의 인덱스
뷰의 초기 고유 클러스터형 인덱스
사용하지 않도록 설정된 클러스터형 인덱스
SQL Server 2017(14.x)의 클러스터형 columnstore 인덱스 및 이전 버전
SQL Server 2016(13.x)의 비클러스터형 columnstore 인덱스 및 이전 버전
기본 테이블이 LOB 데이터 형식(image, ntext, text) 및 공간 데이터 형식을 포함하는 경우 클러스터형 인덱스입니다.
varchar(max) 및 varbinary(max) 열은 인덱스 키의 일부로 사용할 수 없습니다. SQL Server(SQL Server 2012(11.x)부터), Azure SQL Database 및 Azure SQL Managed Instance에서 테이블에 varchar(max) 또는 varbinary(max) 열이 포함된 경우 이 옵션을 사용하여 ONLINE 다른 열을 포함하는 클러스터형 인덱스를 작성하거나 다시 작성할 수 있습니다.
클러스터형 columnstore 인덱스가 있는 테이블의 비클러스터형 인덱스

자세한 내용은 온라인 인덱스 작업이작동하는 방식을 참조하세요.

RESUMABLE = { ON | OFF }

적용: SQL Server 2019(15.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

온라인 인덱스 작업이 다시 시작될 수 있는지 여부를 지정합니다. 자세한 내용은 다시 시작 가능한 인덱스 작업 및 다시 시작 가능한 인덱스 고려 사항을 참조하세요.

켜기

인덱스 작업이 다시 시작될 수 있습니다.
OFF

인덱스 작업이 다시 시작될 수 없습니다.

MAX_DURATION = 에서 사용된 `RESUMABLE = ON` [MINUTES](`ONLINE = ON` 필요)

적용: SQL Server 2019(15.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

일시 중지되기 전에 다시 시작 가능한 인덱스 작업이 실행되는 시간(분)을 지정합니다.

ALLOW_ROW_LOCKS = { ON | OFF }

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

켜기

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

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

ALLOW_PAGE_LOCKS = { ON | OFF }

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

켜기

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

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

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

적용: SQL Server 2019(15.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

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

MAXDOP = max_degree_of_parallelism

인덱스 작업에 대한 최대 병렬 처리 수준 구성 옵션을 재정의합니다. 자세한 내용은 max degree of parallelism 서버 구성 옵션 구성을 참조하세요. MAXDOP 사용하여 인덱스 빌드 작업에 대한 병렬 처리 수준 및 결과 리소스 사용을 제한합니다.

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

1

병렬 계획이 생성되지 않습니다.
>1

병렬 인덱스 작업에 사용되는 최대 병렬 처리 수준을 현재 시스템 워크로드에 따라 지정된 수 이하로 제한합니다.
0(기본값)

현재 시스템 워크로드에 따라 감소하지 않는 한 서버, 데이터베이스 또는 워크로드 그룹 수준에서 지정된 병렬 처리 수준을 사용합니다.

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

참고 항목

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

DATA_COMPRESSION

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

없음

인덱스 또는 지정된 파티션이 압축되지 않습니다. columnstore 인덱스에는 적용되지 않습니다.
ROW

인덱스 또는 지정된 파티션이 행 압축을 사용하여 압축됩니다. columnstore 인덱스에는 적용되지 않습니다.
PAGE

인덱스 또는 지정된 파티션이 페이지 압축을 사용하여 압축됩니다. columnstore 인덱스에는 적용되지 않습니다.
COLUMNSTORE

적용: SQL Server 2014(12.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

클러스터형 columnstore 인덱스 및 비클러스터형 columnstore 인덱스를 모두 포함하는 columnstore 인덱스에만 적용됩니다.
COLUMNSTORE_ARCHIVE

적용: SQL Server 2014(12.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

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

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

XML_COMPRESSION

적용: SQL Server 2022(16.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

하나 이상의 xml 데이터 형식 열을 포함하는 지정된 인덱스에 대한 XML 압축 옵션을 지정합니다. 옵션은 다음과 같습니다.

켜기

인덱스 또는 지정된 파티션이 XML 압축을 사용하여 압축됩니다.
OFF

인덱스 또는 지정된 파티션은 XML 압축을 사용하여 압축되지 않습니다.

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

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>는 다음과 같이 키워드TOON PARTITIONS (6 TO 8)로 구분된 파티션 번호로 지정할 수 있습니다.

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

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

XML_COMPRESSION 옵션을 두 번 이상 지정할 수도 있습니다. 예를 들면 다음과 같습니다.

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

설명

문에 대한 CREATE INDEX 쿼리 계획을 만들 때 쿼리 최적화 프로그램은 테이블 검색을 수행하는 대신 다른 인덱스 검색을 선택할 수 있습니다. 정렬 작업은 경우에 따라 제거될 수 있습니다. 다중 프로세서 컴퓨터 CREATE INDEX 에서 다른 쿼리와 동일한 방식으로 인덱스 만들기와 관련된 검사 및 정렬 작업에 병렬 처리를 사용할 수 있습니다. 자세한 내용은 병렬 인덱스 작업 구성을 참조하세요.

CREATE INDEX 데이터베이스 복구 모델이 대량 로그 또는 단순으로 설정된 경우 작업이 최소 로깅될 수 있습니다.

임시 테이블에 인덱스를 만들 수 있습니다. 테이블이 삭제되거나 범위를 벗어나면 인덱스가 삭제됩니다.

클러스터형 인덱스는 기본 키 제약 조건이 추가될 때 테이블 변수를 기반으로 합니다. 마찬가지로 비클러스터형 인덱스는 고유 제약 조건이 추가될 때 테이블 변수를 기반으로 작성됩니다. 테이블 변수가 범위를 벗어나면 인덱스가 삭제됩니다.

인덱스는 확장 속성을 지원합니다.

CREATE INDEX 는 Microsoft Fabric에서 지원되지 않습니다.

클러스터형 인덱스

테이블(힙)에 클러스터형 인덱스를 만들거나 기존 클러스터형 인덱스를 삭제하고 다시 만들려면 데이터베이스에서 데이터 정렬 및 원본 테이블의 임시 사본이나 기존 클러스터형 인덱스 데이터의 임시 사본을 보관할 수 있는 추가 작업 영역이 필요합니다. 클러스터형 인덱스에 대한 자세한 내용은 클러스터형 인덱스 만들기 및 SQL Server 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

비클러스터형 인덱스

SQL Server 2016(13.x)부터 Azure SQL Database 및 Azure SQL Managed Instance에서 클러스터형 columnstore 인덱스로 저장된 테이블에 비클러스터형 인덱스 만들 수 있습니다. 힙 또는 클러스터형 인덱스로 저장된 테이블에 비클러스터형 인덱스를 처음 만드는 경우 나중에 테이블을 클러스터형 columnstore 인덱스로 변환하면 인덱스가 유지됩니다. 또한 클러스터형 columnstore 인덱스를 다시 작성할 때 비클러스터형 인덱스를 삭제하지 않아도 됩니다.

FILESTREAM_ON 옵션은 클러스터형 columnstore 인덱스로 저장된 테이블에 비클러스터형 인덱스를 만드는 경우 유효하지 않습니다.

고유 인덱스

고유 인덱스가 있는 경우 데이터베이스 엔진은 데이터가 추가되거나 수정될 때마다 중복 값을 확인합니다. 중복 키 값을 생성하는 작업은 롤백되고 데이터베이스 엔진은 오류 메시지를 반환합니다. 이는 데이터 추가 또는 수정 작업에서 여러 행을 변경하지만 중복을 하나만 발생시키는 경우에도 마찬가지입니다. 옵션이 설정된 고유 인덱스가 있을 때 행을 삽입하려고 ON하면 고유 인덱 IGNORE_DUP_KEY 스를 위반하는 행은 무시됩니다.

분할된 인덱스

분할된 인덱스는 분할된 테이블과 비슷한 방법으로 만들어지며 유지 관리됩니다. 그러나 보통 인덱스와 같이 별도의 데이터베이스 개체로 처리됩니다. 분할되지 않은 테이블에 분할된 인덱스가 있을 수 있으며 분할된 테이블에 분할되지 않은 인덱스가 있을 수 있습니다.

분할된 테이블에 인덱스를 만들고 인덱스를 배치할 파일 그룹을 지정하지 않으면 인덱스는 기본 테이블과 같은 방법으로 분할됩니다. 이렇게 되는 이유는 기본적으로 인덱스가 기본 테이블처럼 동일한 파일 그룹에 배치되고 동일한 분할 열을 사용하는 동일한 파티션 구성표의 분할된 테이블에 대해 배치되기 때문입니다. 인덱스에 테이블과 동일한 파티션 구성표 및 분할 열을 사용하는 경우 인덱스는 테이블과 함께 정렬됩니다.

경고

파티션 수가 1,000개를 초과하는 테이블에서 정렬되지 않은 인덱스를 만들거나 다시 작성할 수 있지만 해당 인덱스는 지원되지 않습니다. 그러면 작업 중에 성능이 저하되거나 메모리가 과도하게 소비될 수 있습니다. 파티션 수가 1,000을 초과하는 경우에만 정렬된 인덱스를 사용하는 것이 좋습니다.

비고유 클러스터형 인덱스를 분할하는 경우 데이터베이스 엔진은 기본적으로 지정되지 않은 모든 분할 열을 클러스터형 인덱스 키 목록에 추가합니다.

인덱싱된 뷰는 테이블의 인덱스와 같은 방법으로 분할된 테이블에 만들 수 있습니다. 분할된 인덱스에 대한 자세한 내용은 분할된 테이블 및 인덱스 및 SQL Server 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

인덱스를 만들거나 다시 작성하면 쿼리는 인덱스에 대한 업데이트 통계를 최적화합니다. 분할된 인덱스의 경우 쿼리 최적화 프로그램은 테이블의 모든 행에서 분할되지 않은 인덱스를 검색하는 대신 기본 샘플링 알고리즘을 사용합니다. 테이블의 모든 행을 검사하여 분할된 인덱스에 대한 통계를 얻으려면 CREATE STATISTICS 절에서 UPDATE STATISTICS 또는 FULLSCAN를 사용합니다.

필터링된 인덱스

필터링된 인덱스는 테이블에서 적은 비율의 행을 선택하는 쿼리에 적합한 최적화된 비클러스터형 인덱스입니다. 이 인덱스에서는 필터 조건자를 사용하여 테이블의 일부 데이터를 인덱싱합니다. 잘 디자인된 필터링된 인덱스는 쿼리 성능을 개선하고 스토리지 비용과 유지 관리 비용을 줄일 수 있습니다.

필터링된 인덱스에 필요한 SET 옵션

SET 다음 조건이 발생할 때마다 필수 값 열의 옵션이 필요합니다.

필터링된 인덱스 만들기
, UPDATE, DELETE또는 MERGE 문은 INSERT필터링된 인덱스로 데이터를 수정합니다.

필터링된 인덱스는 쿼리 최적화 프로그램이 쿼리 계획을 작성할 때 사용됩니다.

`SET` 선택	필수 값	기본 서버 값	기본 OLE DB 및 ODBC 값	기본 DB-Library 값
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`ON`	`ON`	`ON`	`OFF`
`ARITHABORT`	`ON`	`ON`	`OFF`	`OFF`
`CONCAT_NULL_YIELDS_NULL`	`ON`	`ON`	`ON`	`OFF`
`NUMERIC_ROUNDABORT`	`OFF`	`OFF`	`OFF`	`OFF`
`QUOTED_IDENTIFIER`	`ON`	`ON`	`ON`	`OFF`

¹ 데이터베이스 호환성 수준이 90 이상으로 설정된 시점으로 암시적으로 설정되도록 설정 ANSI_WARNINGSONARITHABORTON 데이터베이스 호환성 수준이 80 이전 ARITHABORT 으로 설정된 경우 옵션을 명시적으로 설정 ON해야 합니다.

SET 옵션이 올바르지 않으면 다음 조건이 발생할 수 있습니다.

필터링된 인덱스 만들기가 실패합니다.
데이터베이스 엔진은 오류를 생성하고 인덱스의 데이터를 변경하는 , UPDATEDELETE또는 MERGE 문을 롤백INSERT합니다.
쿼리 최적화 프로그램에서 Transact-SQL 문의 실행 계획에 있는 인덱스를 고려하지 않습니다.

필터링된 인덱스에 대한 자세한 내용은 필터링된 인덱스 만들기 및 SQL Server 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

공간 인덱스

공간 인덱스에 대한 자세한 내용은 CREATE SPATIAL INDEX 및 공간 인덱스 개요를 참조하세요.

XML 인덱스

XML 인덱스에 대한 자세한 내용은 CREATE XML INDEX 및 XML 인덱스(SQL Server)를 참조하세요.

인덱스 키 크기

인덱스 키의 최대 크기는 클러스터형 인덱스의 경우 900바이트, 비클러스터형 인덱스의 경우 1,700바이트입니다. (SQL Database 및 SQL Server 2016 (13.x) 이전에는 제한이 항상 900바이트였습니다.) 인덱스를 만들 때 열의 기존 데이터가 제한을 초과하지 않으면 바이트 제한을 초과하는 varchar 열의 인덱스를 만들 수 있습니다. 그러나 총 크기가 제한보다 큰 열에 대한 후속 삽입 또는 업데이트 작업은 실패합니다. 클러스터형 인덱스의 인덱스 키는 할당 단위에 기존 데이터가 있는 ROW_OVERFLOW_DATA 열을 포함할 수 없습니다. varchar 열에 클러스터형 인덱스가 생성되고 기존 데이터가 할당 단위에 있는 IN_ROW_DATA 경우 행에서 데이터를 푸시하는 열에 대한 후속 삽입 또는 업데이트 작업이 실패합니다.

비클러스터형 인덱스는 인덱스의 리프 수준에 키가 아닌(포함된) 열을 포함할 수 있습니다. 이러한 열은 인덱스 키 크기를 계산할 때 데이터베이스 엔진에서 고려하지 않습니다. 자세한 내용은 포함된 열과 SQL Server 인덱스 아키텍처 및 디자인 가이드를 사용하여 인덱스 만들기를 참조하세요.

참고 항목

테이블이 분할된 경우 분할 키 열이 비고유 클러스터형 인덱스에 아직 없으면 데이터베이스 엔진에 의해 해당 열이 인덱스에 추가됩니다. 인덱싱된 열(포함 열 제외)과 추가된 분할 열의 결합된 크기는 비고유 클러스터형 인덱스에서 1,800바이트를 초과할 수 없습니다.

계산 열

계산 열에 인덱스를 만들 수 있습니다. 또한 계산 열에는 속성 PERSISTED이 있을 수 있습니다. 즉, 데이터베이스 엔진 은 계산된 값을 테이블에 저장하고 계산 열이 종속된 다른 열이 업데이트되면 해당 값을 업데이트합니다. 데이터베이스 엔진 은 열에 인덱스를 만들 때와 이 인덱스가 쿼리에서 참조될 때 이러한 지속형 값을 사용합니다.

계산 열을 인덱싱하려면 계산 열이 결정적이고 정확해야 합니다. 그러나 속성을 사용하면 PERSISTED 다음을 포함하도록 인덱싱 가능한 계산 열의 형식이 확장됩니다.

Transact-SQL을 기반으로 하는 계산 열 및 사용자가 결정적으로 표시한 CLR 사용자 정의 형식 메서드 및 CLR 함수
데이터베이스 엔진이 정의한 대로 결정적이지만 정확하지 않은 식을 기반으로 하는 계산 열

지속형 계산 열을 사용하려면 필터링된 인덱스에 대한 이전 섹션 필수 SET 옵션과 같이 다음 SET 옵션을 설정해야 합니다.

UNIQUE 또는 PRIMARY KEY 제약 조건은 인덱싱에 대한 모든 조건을 충족하는 한 계산 열을 포함할 수 있습니다. 특히 계산 열은 결정적이고 정확하거나 결정적이고 지속되어야 합니다. 결정성에 대한 자세한 내용은 결정적 함수 및 비결정적 함수를 참조하세요.

image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) 및 xml 데이터 형식에서 파생된 계산된 열은 계산된 열 데이터 형식이 인덱스 키 열 또는 키가 아닌 열로 허용된다면 키로 인덱싱하거나 키가 아닌 열을 포함할 수 있습니다. 예를 들어 계산된 xml 열에 기본 XML 인덱스를 만들 수 없습니다. 인덱스 키 크기가 900바이트를 초과하면 경고 메시지가 표시됩니다.

계산 열에 인덱스를 만들면 이전에 작동했던 삽입 또는 업데이트 작업이 실패할 수 있습니다. 이러한 오류는 계산 열에 산술 오류가 발생할 때 발생할 수 있습니다.

예를 들어 다음 표에서 계산 열 c 의 식은 행을 삽입 INSERT 할 때 산술 오류가 발생하는 것처럼 보이지만 문은 작동합니다.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

그러나 계산 열 c에 인덱스가 만들어지면 동일한 INSERT 문이 실패합니다.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

자세한 내용은 계산된 열의 인덱스를 참조하세요.

인덱스의 포괄 열

포괄 열이라고 하는 키가 아닌 열은 비클러스터형 인덱스의 리프 수준에 추가되어 쿼리를 포함함으로써 쿼리 성능을 향상시킬 수 있습니다. 즉, 쿼리에서 참조되는 모든 열은 키 열 또는 키가 아닌 열로 인덱스에 포함됩니다. 이렇게 하면 쿼리 최적화 프로그램에서 비클러스터형 인덱스 검색 또는 검색에서 필요한 모든 정보를 가져올 수 있습니다. 테이블 또는 클러스터형 인덱스 데이터에 액세스하지 않습니다. 자세한 내용은 포함된 열과 SQL Server 인덱스 아키텍처 및 디자인 가이드를 사용하여 인덱스 만들기를 참조하세요.

인덱스 옵션 지정

SQL Server 2005(9.x)에서는 새 인덱스 옵션을 도입하고 옵션을 지정하는 방식도 수정했습니다. 이전 버전과 호환되는 구문 WITH option_name 에서 .WITH (option_name = ON) 인덱스 옵션을 설정하면 다음 규칙이 적용됩니다.

새 인덱스 옵션은 WITH (<option_name> = <ON | OFF>)만 사용하여 지정할 수 있습니다.
옵션은 동일한 문에 이전 버전과 호환되는 구문 및 새 구문 모두를 사용하여 지정할 수 없습니다. 예를 들어 WITH (DROP_EXISTING, ONLINE = ON)를 지정하면 문이 실패합니다.
XML 인덱스를 만드는 경우 옵션은 WITH (<option_name> = <ON | OFF>)를 사용하여 지정해야 합니다.

DROP_EXISTING 절

DROP_EXISTING 절을 사용하여 인덱스 다시 빌드, 열 추가 또는 삭제, 옵션 수정, 열 정렬 순서 수정 또는 파티션 구성표나 파일 그룹 변경 등의 작업을 수행할 수 있습니다.

인덱스가 제약 UNIQUE 조건을 적용하고 인덱스 PRIMARY KEY 정의가 어떤 방식으로도 변경되지 않으면 인덱스가 삭제되고 기존 제약 조건을 유지하여 다시 생성됩니다. 그러나 인덱스 정의가 변경되면 이 문은 실패합니다. 또는 UNIQUE 제약 조건의 PRIMARY KEY 정의를 변경하려면 제약 조건을 삭제하고 새 정의에 제약 조건을 추가합니다.

비클러스터형 인덱스가 있는 테이블에서 동일하거나 서로 다른 키 집합을 사용하여 클러스터형 인덱스를 다시 만들 때 DROP_EXISTING을 사용하면 성능이 향상됩니다. DROP_EXISTING은 이전에 클러스터형 인덱스에 대해 DROP INDEX 문을 실행한 후 새로 클러스터형 인덱스를 만드는 CREATE INDEX 문을 실행하는 것과 같습니다. 비클러스터형 인덱스는 인덱스 정의가 변경된 경우에만 다시 한번 만들어집니다. 인덱스 정의에서 원래 인덱스와 동일한 인덱스 이름, 키 및 파티션 열, 고유성 특성, 정렬 순서를 사용하는 경우 DROP_EXISTING 절은 비클러스터형 인덱스를 다시 빌드하지 않습니다.

비클러스터형 인덱스는 다시 만들지 여부에 관계없이 항상 원래 파일 그룹 또는 파티션 구성표에 남아 있으며 원래 파티션 함수를 사용합니다. 클러스터형 인덱스를 다른 파일 그룹이나 파티션 구성표에 다시 만들면 비클러스터형 인덱스는 클러스터형 인덱스의 새 위치와 일치하도록 이동되지 않습니다. 따라서 비클러스터형 인덱스가 이전에 클러스터형 인덱스에 맞춰진 경우에도 더 이상 해당 인덱스와 정렬되지 않을 수 있습니다. 분할된 인덱스 맞춤에 대한 자세한 내용은 분할된 테이블 및 인덱스를 참조하세요.

DROP_EXISTING 인덱스 문이 비클러스터 ONLINE 형 인덱스를 지정하고 옵션이 설정되지 않는 한 동일한 인덱스 키 열이 동일한 순서로 동일한 오름차순 또는 내림차순으로 사용되는 경우 절은 OFF데이터를 다시 정렬하지 않습니다. 클러스터형 인덱스가 비활성화 CREATE INDEX WITH DROP_EXISTING 된 경우 작업을 다음으로 ONLINE 설정 OFF하여 수행해야 합니다. 비클러스터형 인덱스가 비활성화되어 있고 비활성화된 클러스터형 인덱 CREATE INDEX WITH DROP_EXISTING 스와 연결되지 않은 경우 이 작업은 설정 또는 ON로 설정 OFF 하여 수행할 ONLINE 수 있습니다.

참고 항목

익스텐트가 128 이상인 인덱스를 삭제하거나 다시 작성하면 데이터베이스 엔진은 트랜잭션이 커밋될 때까지 실제 페이지 할당 취소 및 이와 관련된 잠금을 지연합니다. 자세한 내용은 지연 할당 취소 참조하세요.

ONLINE 옵션

다음 지침은 인덱스 작업을 온라인 상태로 수행할 때 적용됩니다.

온라인 인덱스 작업이 진행되는 동안에는 기본 테이블을 변경하거나 자르거나 삭제할 수 없습니다.
인덱스 작업 중에 임시 디스크 공간이 추가로 필요합니다.
온라인 작업은 분할된 인덱스 및 지속형 계산 열이 들어 있는 인덱스 또는 포괄 열에서 수행될 수 있습니다.
WAIT_AT_LOW_PRIORITY 인수 옵션을 사용하면 잠금을 기다릴 때 인덱스 작업이 진행되는 방식을 결정할 수 있습니다Sch-M. 자세한 내용은 WAIT_AT_LOW_PRIORITY 참조하세요.

자세한 내용은 온라인인덱스 작업 수행을 참조하세요.

다시 시작 가능한 인덱스 작업

적용: SQL Server 2019(15.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

온라인 인덱스 만들기 작업을 다시 시작 가능하게 만들 수 있습니다. 즉, 인덱스 빌드를 중지하고 나중에 중지된 지점에서 다시 시작할 수 있습니다. 인덱스 빌드를 다시 시작하는 것으로 실행하려면 옵션을 지정합니다 RESUMABLE = ON .

다음 지침은 다시 시작 가능한 인덱스 작업에 적용됩니다.

RESUMABLE 옵션을 사용하려면 ONLINE 옵션도 사용해야 합니다.
RESUMABLE 옵션은 지정된 인덱스에 대한 메타데이터에 유지되지 않으며 현재 DDL 문의 기간에만 적용됩니다. 그러므로 다시 시작이 가능하도록 하려면 RESUMABLE = ON 절을 명시적으로 지정해야 합니다.
MAX_DURATION 옵션은 다음 두 가지 컨텍스트에서 지정할 수 있습니다.
- MAX_DURATION 이 옵션에 RESUMABLE 대해 다시 작성할 인덱스의 시간 간격을 지정합니다. 이 시간이 경과하고 인덱스 다시 작성이 계속 실행 중인 경우 일시 중지됩니다. 일시 중지된 인덱스 다시 작성을 다시 시작하는 시기를 결정합니다. MAX_DURATION(분)은 0분보다 크고 1주일보다 작거나 같아야 합니다(7 * 24 * 60 = 10080분). 인덱스 작업의 긴 일시 중지는 원래 인덱스와 새로 만든 인덱스 모두 디스크 공간이 필요하고 DML 작업으로 업데이트해야 하므로 데이터베이스 디스크 용량뿐만 아니라 특정 테이블의 DML 성능에 눈에 띄게 영향을 줄 수 있습니다. MAX_DURATION 옵션을 생략하면 인덱스 작업이 완료될 때까지 또는 오류가 발생할 때까지 계속됩니다.
- MAX_DURATION 옵션에 대한 WAIT_AT_LOW_PRIORITY 작업을 수행하기 전에 인덱스 작업이 차단된 경우 우선 순위가 낮은 잠금을 사용하여 대기하는 시간을 지정합니다. 자세한 내용은 온라인 인덱스 작업 WAIT_AT_LOW_PRIORITY 참조하세요.
인덱스 작업을 즉시 일시 중지하려면 ALTER INDEX PAUSE 명령을 실행하거나 KILL <session_id> 명령을 실행할 수 있습니다.
동일한 매개 변수를 사용하여 원래 CREATE INDEX 문을 다시 실행하면 일시 중지된 인덱스 빌드 작업이 다시 시작됩니다. 문을 실행하여 일시 중지된 인덱스 빌드 작업을 다시 시작할 수도 있습니다 ALTER INDEX RESUME .
ABORT 명령은 인덱스 빌드를 실행하는 세션을 종료하고 인덱스 작업을 취소합니다. 중단된 인덱스 작업은 다시 시작할 수 없습니다.

다시 시작 가능한 인덱스 작업은 완료, 일시 중지 또는 실패할 때까지 실행됩니다. 작업이 일시 중지된 경우 작업이 일시 중지되었고 인덱스 만들기가 완료되지 않았음을 나타내는 오류가 발생합니다. 작업이 실패하는 경우 오류도 발생합니다.

인덱스 작업이 다시 시작 가능한 작업으로 실행되는지 확인하고 현재 실행 상태를 확인하려면 sys.index_resumable_operations 카탈로그 뷰를 사용합니다.

리소스

다시 시작 가능한 인덱스 작업에는 다음 리소스가 필요합니다.

빌드가 일시 중지되는 시간을 포함하여 인덱스가 빌드되는 것을 유지하는 데 필요한 추가 공간입니다.
정렬 단계 중의 추가 로그 처리량. 다시 시작 가능한 인덱스의 전체 로그 공간 사용량이 일반 온라인 인덱스 만들기에 비교해 적으므로 이 작업 중에 로그가 잘릴 수 있습니다.
인덱스 작업이 일시 중지되는 동안 생성되는 인덱스와 연결된 테이블을 수정하려는 DDL 문은 허용되지 않습니다.
일시 중지된 동안 및 작업이 실행되는 동안의 작업 기간에 작성 중인 인덱스에 대한 고스트 정리는 차단됩니다.
테이블에 LOB 열이 포함된 경우 다시 시작 가능한 클러스터형 인덱스 빌드에는 작업 시작 시 스키마 수정(Sch-M) 잠금이 필요합니다.

현재 기능 제한 사항

다시 시작 가능한 인덱스 만들기 작업에는 다음과 같은 제한 사항이 있습니다.

다시 시작 가능한 온라인 인덱스 만들기 작업이 일시 중지된 후에는 초기 값을 MAXDOP 변경할 수 없습니다.
SORT_IN_TEMPDB = ON 옵션은 다시 시작 가능한 인덱스 작업에 지원되지 않습니다.
RESUMABLE = ON 포함된 DDL 명령은 명시적 트랜잭션 내에서 실행할 수 없습니다.
다음을 포함하는 다시 시작하는 인덱스를 만들 수 없습니다.
- 열 또는 timestamp (rowversion) 열을 키 열로 계산합니다.
- LOB 열을 포함된 열로 지정합니다.
다시 시작 가능한 인덱스 작업은 다음에서 지원되지 않습니다.
- ALTER INDEX REBUILD ALL 명령
- ALTER TABLE REBUILD 명령
- Columnstore 인덱스
- 필터링된 인덱스
- 비활성화된 인덱스

온라인 인덱스 작업에 대한 WAIT_AT_LOW_PRIORITY

적용: SQL Server 2022(16.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

이 옵션을 사용하지 WAIT_AT_LOW_PRIORITY 않으면 인덱스 만들기 작업이 시작되고 완료되려면 테이블 또는 인덱스에 대한 잠금을 보유하는 모든 활성 차단 트랜잭션이 완료되어야 합니다. 온라인 인덱스 작업이 시작되고 완료되기 전에 테이블에 대한 공유(S) 또는 스키마 수정(Sch-M) 잠금을 획득하고 잠시 동안 보관해야 합니다. 잠금이 짧은 시간 동안만 유지되더라도 워크로드 처리량에 큰 영향을 미치거나 쿼리 대기 시간을 늘리거나 실행 시간 초과가 발생할 수 있습니다.

이러한 문제를 방지하기 위해 WAIT_AT_LOW_PRIORITY 옵션을 사용하면 온라인 인덱스 작업을 시작하고 완료하는 데 필요한 S 또는 Sch-M 잠금의 동작을 관리하고 세 가지 옵션 중에서 선택할 수 있습니다. 모든 경우에 MAX_DURATION = n [minutes] 지정된 대기 시간 동안 인덱스 작업을 포함하는 차단이 없으면 인덱스 작업이 즉시 진행됩니다.

WAIT_AT_LOW_PRIORITY 낮은 우선 순위 잠금을 사용하여 온라인 인덱스 작업을 대기하게 하므로 정상 우선 순위 잠금을 사용하는 다른 작업이 그 동안 계속 진행할 수 있습니다. WAIT_AT_LOW_PRIORITY 옵션을 생략하면 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)와 동일합니다.

MAX_DURATION = 시간 [MINUTES]

온라인 인덱스 작업이 낮은 우선 순위 잠금을 사용하여 대기하는 대기 시간(분 단위로 지정된 정수 값)입니다. 작업이 시간 동안 MAX_DURATION 차단되면 지정된 ABORT_AFTER_WAIT 작업이 실행됩니다. MAX_DURATION 시간은 항상 분 단위이며 단어를 MINUTES 생략할 수 있습니다.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

NONE: 정상 우선 순위로 잠금을 계속 기다립니다.
SELF: 아무 작업도 수행하지 않고 현재 실행 중인 온라인 인덱스 작업을 종료합니다. SELF 0이면 MAX_DURATION 옵션을 사용할 수 없습니다.
BLOCKERS: 작업을 계속할 수 있도록 온라인 인덱스 작업을 차단하는 모든 사용자 트랜잭션을 종료합니다. BLOCKERS 옵션을 사용하려면 CREATE INDEX 또는 ALTER INDEX 문을 실행하는 보안 주체에게 ALTER ANY CONNECTION 권한이 있어야 합니다.

다음 확장 이벤트를 사용하여 낮은 우선 순위에서 잠금을 기다리는 인덱스 작업을 모니터링할 수 있습니다.

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

행 및 페이지 잠금 옵션

ALLOW_ROW_LOCKS = ON 및 ALLOW_PAGE_LOCK = ON인 경우 인덱스에 액세스할 때 행, 페이지 및 테이블 수준 잠금이 허용됩니다. 데이터베이스 엔진은 적절한 잠금을 선택하고 행 또는 페이지 잠금에서 테이블 잠금으로 잠금을 에스컬레이션할 수 있습니다.

ALLOW_ROW_LOCKS = OFF 및 ALLOW_PAGE_LOCK = OFF인 경우 인덱스에 액세스할 때 테이블 수준 잠금만 허용됩니다.

경고

인덱스에서 행 또는 페이지 잠금을 사용하지 않도록 설정하는 것은 권장되지 않습니다. 동시성 관련 문제가 발생할 수 있으며 특정 기능을 사용할 수 없을 수 있습니다. 예를 들어 ALLOW_PAGE_LOCKSOFF설정되면 인덱스 다시 구성할 수 없습니다.

순차 키

적용 대상: SQL Server 2019(15.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance에서.

마지막 페이지 삽입 경합은 다수의 동시 스레드가 순차 키를 사용하여 인덱스에 행을 삽입하려고 할 때 발생하는 일반적인 성능 문제입니다. ID 열이나 기본적으로 현재 날짜/시간으로 설정되는 날짜와 같이 항상 증가(또는 감소)하는 값이 선행 키 열에 포함되는 경우 인덱스가 순차적이라고 간주됩니다. 삽입되는 키는 순차적이므로 모든 새 행은 인덱스 구조의 끝에 삽입됩니다. 즉, 동일한 페이지에 삽입됩니다. 이로 인해 메모리의 페이지에 대한 경합이 발생하며, 이는 문제의 페이지에 대한 래치를 획득하기 위해 대기하는 여러 스레드로 관찰될 수 있습니다. 해당 대기 유형은 .입니다 PAGELATCH_EX.

OPTIMIZE_FOR_SEQUENTIAL_KEY 인덱스 옵션을 사용하도록 설정하면 데이터베이스 엔진 내에서 인덱스에 대한 높은 동시성 삽입 처리량을 향상하는 데 도움이 되는 최적화가 사용됩니다. 순차 키가 있어서 마지막 페이지 삽입 경합이 발생하기 쉬운 인덱스에 사용되지만, B-트리 인덱스 구조의 다른 영역에 핫 스폿이 있는 인덱스에도 도움이 될 수 있습니다.

참고 항목

데이터 압축

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

다음은 데이터 압축을 사용할 때 인덱스 빌드 작업의 컨텍스트에서 고려해야 할 핵심 사항입니다.

압축하면 한 페이지에 더 많은 행을 저장할 수 있지만 최대 행 크기는 변경되지 않습니다.
인덱스의 비-리프 페이지는 압축된 페이지가 아니지만 행은 압축할 수 있습니다.
각각의 비클러스터형 인덱스에는 개별 압축 설정이 있으며 기본 테이블의 압축 설정을 상속하지 않습니다.
힙에 클러스터형 인덱스를 만드는 경우 이 클러스터형 인덱스는 다른 압축 상태를 지정하지 않는 한 힙의 압축 상태를 상속합니다.

압축 상태를 변경하면 테이블, 인덱스 또는 파티션의 공간 사용량에 미치는 영향을 평가하려면 sp_estimate_data_compression_savings 저장 프로시저를 사용합니다.

XML 압축

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

대부분의 데이터 압축 고려 사항은 XML 압축에 적용됩니다. 다음 고려 사항도 알고 있어야 합니다.

파티션 목록이 지정되면 개별 파티션에서 XML 압축을 사용할 수 있습니다. 파티션 목록을 지정하지 않으면 모든 파티션이 XML 압축을 사용하도록 설정됩니다. 테이블 또는 인덱스가 생성될 때 달리 지정하지 않는 한 XML 데이터 압축은 사용하지 않도록 설정됩니다. 테이블을 수정할 경우에는 달리 지정하지 않는 한 기존 압축이 유지됩니다.
범위를 벗어난 파티션 목록 또는 파티션을 지정하면 오류가 생성됩니다.
클러스터형 인덱스가 힙에 생성될 때 대체 압축 옵션이 지정되지 않는 한 클러스터형 인덱스는 힙의 XML 압축 상태를 상속합니다.
힙의 XML 압축 설정을 변경하는 경우 힙의 새 행 위치에 대한 포인터를 포함하도록 테이블의 모든 비클러스터형 인덱스를 다시 작성해야 합니다.
온라인이나 오프라인으로 XML 압축을 사용하거나 사용하지 않도록 설정할 수 있습니다. 힙에서 압축을 사용하도록 설정하는 것은 온라인 작업의 경우 단일 스레드입니다.
분할된 테이블에서 파티션의 XML 압축 상태를 확인하려면 카탈로그 뷰의 sys.partitions 열을 사용합니다xml_compression.

인덱스 통계

rowstore 인덱스를 만들 때 데이터베이스 엔진은 인덱스의 키 열에 대한 통계 도 만듭니다. sys.stats 카탈로그 뷰의 통계 개체 이름이 인덱스의 이름과 일치합니다. 분할되지 않은 인덱스의 경우 통계는 데이터의 전체 검사를 사용하여 작성됩니다. 분할된 인덱스의 경우 통계는 기본 샘플링 알고리즘을 사용하여 빌드됩니다.

columnstore 인덱스를 만들 때 데이터베이스 엔진은 sys.stats 에도 통계 개체를 만듭니다. 이 통계 개체는 히스토그램 및 밀도 벡터와 같은 통계 데이터를 포함하지 않습니다. 데이터베이스를 스크립팅하여 데이터베이스 복제본을 만들 때 사용됩니다. 이때 및 명령은 세그먼트, DBCC SHOW_STATISTICS 사전 및 UPDATE STATISTICS ... WITH STATS_STREAM 델타 저장소 크기와 같은 columnstore 메타데이터를 가져와 columnstore 인덱스의 통계에 추가하는 데 사용됩니다. 이 메타데이터는 일반 데이터베이스에 대한 쿼리 컴파일 시간에 동적으로 가져오지만 데이터베이스 복제본에 대한 통계 개체에서 제공됩니다. UPDATE STATISTICS 명령은 다른 시나리오에서 columnstore 인덱스의 통계 개체에 대해 지원되지 않습니다.

사용 권한

ALTER 테이블 또는 뷰 또는 고정 데이터베이스 역할의 멤버 자격에 대한 db_ddladmin 권한이 필요합니다.

제한 사항

Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)에서는 다음을 만들 수 없습니다.

columnstore 인덱스가 이미 존재하는 경우 데이터 웨어하우스 테이블에 대한 클러스터형 또는 비클러스터형 rowstore 인덱스. 이 동작은 rowstore와 columnstore 인덱스가 같은 테이블에 공존할 수 있게 해주는 SMP SQL Server와 다릅니다.
뷰에서 인덱스를 만들 수 없습니다.

메타데이터

기존 인덱스에 대한 자세한 정보를 보려면 sys.indexes 카탈로그 뷰를 쿼리할 수 있습니다.

버전 참고 사항

Azure SQL Database는 PRIMARY이외의 파일 그룹을 지원하지 않습니다.
Azure SQL Database 및 Azure SQL Managed Instance는 FILESTREAM 옵션을 지원하지 않습니다.
Columnstore 인덱스는 SQL Server 2012(11.x) 이전에는 사용할 수 없습니다.
다시 시작 가능한 인덱스 작업은 SQL Server 2017(14.x), Azure SQL Database 및 Azure SQL Managed Instance에서 사용할 수 있습니다.

예제: 모든 버전. AdventureWorks 데이터베이스를 사용합니다.

A. 간단한 비클러스터형 rowstore 인덱스 만들기

다음 예제에서는 VendorID 테이블의 Purchasing.ProductVendor 열에 비클러스터형 인덱스를 만듭니다.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. 간단한 비클러스터형 rowstore 복합 인덱스 만들기

다음 예제에서는 SalesQuota 테이블의 SalesYTD 및 Sales.SalesPerson 열에 비클러스터형 복합 인덱스를 만듭니다.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. 다른 데이터베이스의 테이블에 인덱스 만들기

다음 예제에서는 VendorID 데이터베이스에 있는 ProductVendor 테이블의 Purchasing 열에 클러스터형 인덱스를 만듭니다.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D. 인덱스에 열 추가

다음 예제에서는 dbo.FactFinance 테이블의 열 2개를 사용하여 IX_FF 인덱스를 만듭니다. 다음 명령문은 하나 이상의 열을 포함한 인덱스를 다시 만들며 기존 이름을 유지합니다.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

예제: SQL Server, Azure SQL Database

E. 고유한 비클러스터형 인덱스 만들기

다음 예에서는 Name 데이터베이스에 있는 Production.UnitMeasure 테이블의 AdventureWorks2022 열에 고유한 비클러스터형 인덱스를 만듭니다. 인덱스는 Name 열에 삽입된 데이터의 고유성을 강제 적용합니다.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

다음 쿼리는 기존 행과 동일한 값을 가진 행을 삽입하여 고유성 제약 조건을 테스트합니다.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

결과 오류 메시지는 다음과 같습니다.

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. IGNORE_DUP_KEY 옵션 사용

다음 예에서는 IGNORE_DUP_KEY 옵션을 먼저 ON으로 설정한 후 다시 OFF로 설정한 상태에서 여러 행을 임시 테이블에 삽입했을 때 미치는 영향을 보여 줍니다. 두 번째 여러 행 #Test 문이 실행될 때 의도적으로 중복 값을 발생시키는 INSERT 테이블에 단일 행을 삽입합니다. 테이블의 행 수가 삽입된 행 수를 반환합니다.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

두 번째 INSERT 문의 결과는 다음과 같습니다.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

고유성 제약 조건을 위반하지 않은 Production.UnitMeasure 테이블에서 삽입된 행이 성공적으로 삽입되었습니다. 경고가 발생하고 중복된 행이 무시되었지만 전체 트랜잭션은 롤백되지 않았습니다.

같은 문이 다시 실행되지만 IGNORE_DUP_KEY를 OFF로 설정한 상태입니다.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

두 번째 INSERT 문의 결과는 다음과 같습니다.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Production.UnitMeasure 테이블에서 오직 한 행만 UNIQUE 인덱스 제약 조건을 위반했지만 이 테이블에서 어떤 행도 삽입되지 않았습니다.

G. DROP_EXISTING을 사용하여 인덱스 삭제 및 다시 만들기

다음 예에서는 ProductID 옵션을 사용하여 Production.WorkOrder 데이터베이스에 있는 AdventureWorks2022 테이블의 DROP_EXISTING 열에서 기존 인덱스를 삭제하고 다시 만듭니다. FILLFACTOR 및 PAD_INDEX 옵션도 설정됩니다.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. 뷰에 인덱스 만들기

다음 예에서는 뷰를 만들고 이 뷰에 인덱스를 만듭니다. 인덱싱된 뷰를 사용하는 두 개의 쿼리가 포함되어 있습니다.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

9\. (키가 아닌) 포함 열이 있는 인덱스 만들기

다음 예에서는 1개의 키 열(PostalCode)과 4개의 키가 아닌 열(AddressLine1, AddressLine2, City, StateProvinceID)이 있는 비클러스터형 인덱스를 만듭니다. 인덱스에서 처리하는 쿼리가 이어집니다. 쿼리 최적화 프로그램에서 선택한 인덱스를 표시하려면 쿼리를 실행하기 전에 SQL Server Management Studio의 쿼리 메뉴에서 실제 실행 계획 표시를 선택합니다.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. 분할된 인덱스 만들기

다음은 TransactionsPS1 데이터베이스에 있는 기존 파티션 구성표인 AdventureWorks2022에 분할된 비클러스터형 인덱스를 만드는 예입니다. 이 예에서는 분할된 인덱스 샘플이 설치되었다고 가정합니다.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

11. 필터링된 인덱스 만들기

다음 예에서는 AdventureWorks2022 데이터베이스의 Production.BillOfMaterials 테이블에 필터링된 인덱스를 만듭니다. 필터 조건자는 필터링된 인덱스에 키 열이 아닌 열을 포함할 수 있습니다. 이 예에서 조건자는 EndDate가 NULL이 아닌 행만 선택합니다.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

12. 압축 인덱스 만들기

다음 예에서는 행 압축을 사용하여 분할되지 않은 테이블에 인덱스를 만듭니다.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

다음 예에서는 인덱스의 모든 파티션에서 행 압축을 사용하여 분할된 테이블에 인덱스를 만듭니다.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

다음 예에서는 인덱스의 1 파티션에서 페이지 압축을 사용하고 2-4 파티션에서 행 압축을 사용하여 분할된 테이블에 인덱스를 만듭니다.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

13. XML 압축을 사용하여 인덱스 만들기

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

다음 예에서는 XML 압축을 사용하여 분할되지 않은 테이블에 인덱스를 만듭니다. 인덱스에서 하나 이상의 열이 xml 데이터 형식이어야 합니다.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

다음 예에서는 인덱스의 모든 파티션에서 XML 압축을 사용하여 분할된 테이블에 인덱스를 만듭니다.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

14. 다시 시작 가능한 인덱스 작업 만들기, 다시 시작, 일시 중지 및 중단

적용: SQL Server 2019(15.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

15. 다양한 낮은 우선 순위 잠금 옵션을 사용한 CREATE INDEX

다음 예제에서는 WAIT_AT_LOW_PRIORITY 옵션을 사용하여 다양한 차단 처리 전략을 지정합니다.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

다음 예제에서는 RESUMABLE 옵션을 사용하고 두 가지 MAX_DURATION 값을 지정합니다. 첫 번째 값은 ABORT_AFTER_WAIT 옵션에 적용되고, 두 번째 값은 RESUMABLE 옵션에 적용됩니다.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

예: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

16. 기본 구문

다시 시작 가능한 인덱스 작업 만들기, 다시 시작, 일시 중지 및 중단

적용: SQL Server 2019(15.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

17. 현재 데이터베이스의 테이블에 비클러스터형 인덱스 만들기

다음 예에서는 VendorID 테이블의 ProductVendor 열에 비클러스터형 인덱스를 만듭니다.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

18. 다른 데이터베이스의 테이블에 클러스터형 인덱스 만들기

다음 예에서는 VendorID 데이터베이스에 있는 ProductVendor 테이블의 Purchasing 열에 비클러스터형 인덱스를 만듭니다.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S.는 테이블에 순서가 지정된 클러스터형 인덱스 만들기

다음 예제에서는 c1 데이터베이스에 있는 c2 테이블의 T1 및 MyDB 열에 순서가 지정된 클러스터형 인덱스를 만듭니다.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

20. 테이블에서 CCI를 순서가 지정된 클러스터형 인덱스로 변환

다음 예제에서는 MyOrderedCCI 데이터베이스에 있는 c1 테이블의 c2 및 T2 열에서 기존 클러스터형 columnstore 인덱스를 MyDB라는 순서가 지정된 클러스터형 columnstore 인덱스로 변환합니다.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

다음을 통해 공유

CREATE INDEX(Transact-SQL)

Syntax

SQL Server, Azure SQL Database 및 Azure SQL Managed Instance용 구문

이전 버전과 호환되는 관계형 인덱스

Azure Synapse Analytics 및 병렬 데이터 웨어하우스용 구문

인수

UNIQUE

CLUSTERED

NONCLUSTERED

index_name

column

[ ASC | DESC ]

INCLUDE (column [ ,... n ] )

WHERE <filter_predicate>

ON partition_scheme_name ( column_name )

ON filegroup_name

ON [기본값]

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

<object>::=

database_name

schema_name

table_or_view_name

<relational_index_option>::=

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

SORT_IN_TEMPDB = { ON | OFF }

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF}

STATISTICS_INCREMENTAL = {ON | OFF}

DROP_EXISTING = { ON | OFF }

ONLINE = { ON | OFF }

RESUMABLE = { ON | OFF }

MAX_DURATION = 에서 사용된 RESUMABLE = ON [MINUTES](ONLINE = ON 필요)

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

MAXDOP = max_degree_of_parallelism

DATA_COMPRESSION

XML_COMPRESSION

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

설명

클러스터형 인덱스

비클러스터형 인덱스

고유 인덱스

분할된 인덱스

필터링된 인덱스

필터링된 인덱스에 필요한 SET 옵션

공간 인덱스

XML 인덱스

인덱스 키 크기

계산 열

인덱스의 포괄 열

인덱스 옵션 지정

DROP_EXISTING 절

ONLINE 옵션

다시 시작 가능한 인덱스 작업

리소스

현재 기능 제한 사항

온라인 인덱스 작업에 대한 WAIT_AT_LOW_PRIORITY

행 및 페이지 잠금 옵션

순차 키

데이터 압축

XML 압축

인덱스 통계

사용 권한

제한 사항

메타데이터

버전 참고 사항

예제: 모든 버전. AdventureWorks 데이터베이스를 사용합니다.

A. 간단한 비클러스터형 rowstore 인덱스 만들기

B. 간단한 비클러스터형 rowstore 복합 인덱스 만들기

C. 다른 데이터베이스의 테이블에 인덱스 만들기

D. 인덱스에 열 추가

예제: SQL Server, Azure SQL Database

E. 고유한 비클러스터형 인덱스 만들기

F. IGNORE_DUP_KEY 옵션 사용

G. DROP_EXISTING을 사용하여 인덱스 삭제 및 다시 만들기

H. 뷰에 인덱스 만들기

9\. (키가 아닌) 포함 열이 있는 인덱스 만들기

MAX_DURATION = 에서 사용된 `RESUMABLE = ON` [MINUTES](`ONLINE = ON` 필요)