CREATE INDEX(Transact-SQL)

  • 아티클

업데이트: 2006년 4월 14일

지정된 테이블 또는 뷰에 관계형 인덱스를 만들거나 지정된 테이블에 XML 인덱스를 만듭니다. 테이블에 데이터가 존재하기 전에 인덱스를 만들 수 있습니다. 정규화된 데이터베이스 이름을 지정하여 다른 데이터베이스에 있는 테이블이나 뷰에 인덱스를 만들 수도 있습니다.

항목 링크 아이콘Transact-SQL 구문 표기 규칙

구문

Create Relational Index CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_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 }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Create XML Index CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

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 
}

인수

  • UNIQUE
    테이블 또는 뷰에 고유 인덱스를 만듭니다. 고유 인덱스는 두 개의 행에 동일한 인덱스 키 값을 가질 수 없습니다. 뷰의 클러스터형 인덱스는 고유해야 합니다.

    IGNORE_DUP_KEY가 ON으로 설정되어 있는지 여부에 상관없이 SQL Server 2005 데이터베이스 엔진에서는 이미 중복 값이 들어 있는 열에 고유 인덱스를 만들 수 없습니다. 이 작업을 시도하면 데이터베이스 엔진은 오류 메시지를 표시합니다. 열에 고유 인덱스를 만들려면 먼저 중복 값을 제거해야 합니다. 고유 인덱스를 만들 때 여러 개의 Null 값은 중복 값으로 취급되므로 고유 인덱스에서 사용되는 열은 NOT NULL로 설정해야 합니다.

  • CLUSTERED
    키 값의 논리적 순서가 테이블에 있는 해당 행의 물리적 순서를 결정하는 인덱스를 만듭니다. 클러스터형 인덱스의 최하위 수준인 리프 수준에는 테이블의 실제 데이터 행이 있습니다. 테이블 또는 뷰는 한 번에 클러스터형 인덱스 하나만 허용합니다. 자세한 내용은 클러스터형 인덱스 구조를 참조하십시오.

    고유 클러스터형 인덱스가 있는 뷰를 인덱싱된 뷰라고 합니다. 뷰에서 고유 클러스터형 인덱스를 만들면 물리적으로 뷰를 구체화합니다. 뷰에 다른 인덱스를 정의하려면 먼저 고유 클러스터형 인덱스를 만들어야 합니다. 자세한 내용은 인덱싱된 뷰 디자인을 참조하십시오.

    비클러스터형 인덱스를 만들기 전에 항상 클러스터형 인덱스를 만듭니다. 테이블에 있는 기존의 비클러스터형 인덱스는 클러스터형 인덱스를 만들 때 다시 작성됩니다.

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

    [!참고] 클러스터형 인덱스의 리프 수준과 데이터 페이지는 정의가 동일하므로, 클러스터형 인덱스를 만들고 ON partition_scheme_name 또는 ON filegroup_name 절을 사용하여 효과적으로 테이블을 만든 파일 그룹의 테이블을 새 파티션 구성표 또는 파일 그룹으로 이동합니다. 특정 파일 그룹에서 테이블이나 인덱스를 만들기 전에 사용 가능한 파일 그룹과 인덱스를 만들 공간이 충분한지 확인합니다. 자세한 내용은 인덱스 디스크 공간 요구 사항 확인을 참조하십시오.

  • NONCLUSTERED
    테이블의 논리적 순서를 지정하는 인덱스를 만듭니다. 비클러스터형 인덱스에서는 데이터 행의 물리적 순서가 인덱싱된 순서와 다릅니다. 자세한 내용은 비클러스터형 인덱스 구조를 참조하십시오.

    각 테이블에는 비클러스터형 인덱스를 PRIMARY KEY와 UNIQUE 제약 조건을 사용하여 암시적으로 만들거나 CREATE INDEX를 사용하여 명시적으로 만드는 방법에 관계없이 249개까지 만들 수 있습니다.

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

    기본값은 NONCLUSTERED입니다.

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

    기본 XML 인덱스 이름은 #, ##, @ 또는 @@ 문자로 시작할 수 없습니다.

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

    단일 복합 인덱스 키에 최대 16개의 열을 결합할 수 있으며 복합 인덱스 키의 모든 열은 동일한 테이블 또는 뷰에 있어야 합니다. 결합된 인덱스 값의 최대 허용 크기는 900바이트입니다. 복합 인덱스의 변수 유형 열에 대한 자세한 내용은 주의 섹션을 참조하십시오.

    LOB(Large Object) 데이터 형식 ntext, text, varchar(max), nvarchar(max), varbinary(max), xml 또는 image이 있는 열은 인덱스의 키 열로 지정할 수 없습니다. 또한 CREATE INDEX 문에 참조되지 않은 경우에도 뷰 정의에 ntext, text 또는 image 열을 포함할 수 없습니다.

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

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

  • INCLUDE **(**column [ ,... n ] )
    비클러스터형 인덱스의 리프 수준에 키가 아닌 열을 추가할 것을 지정합니다. 비클러스터형 인덱스는 고유하거나 고유하지 않을 수 있습니다.

    키가 아닌 포괄 열의 최대 수는 1,023이며 최소 수는 1입니다.

    열 이름을 INCLUDE 목록에 반복 사용할 수 없으며 키 열과 키가 아닌 열로 동시에 사용할 수 없습니다. 자세한 내용은 포괄 열이 있는 인덱스를 참조하십시오.

    text, ntext 및 image를 제외한 모든 데이터 형식을 사용할 수 있습니다. 지정된 키가 아닌 열 중 하나가 varchar(max), nvarchar(max) 또는 varbinary(max) 데이터 형식인 경우 인덱스를 오프라인(ONLINE = OFF) 상태로 만들거나 다시 작성해야 합니다.

    결정적이면서 정확하거나 정확하지 않은 계산 열은 포괄 열이 될 수 있습니다. image, ntext, text, varchar(max), nvarchar(max), varbinary(max) 및 xml 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 포괄 열로 허용되는 동안 키가 아닌 포괄 열이 될 수 있습니다. 자세한 내용은 계산 열에 인덱스 만들기를 참조하십시오.

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

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

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

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

    분할된 인덱스에 대한 자세한 내용은 분할 인덱스에 대한 특수 지침을 참조하십시오.

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

  • ON "default"
    주어진 파일 그룹에 지정된 인덱스를 만듭니다.

    이 컨텍스트에서 default 용어는 키워드가 아닙니다. 이것은 기본 파일 그룹에 대한 식별자이며 ON "default" 또는 ON **[default]**와 같이 구분되어야 합니다. "default"를 지정하면 현재 세션에 대해 QUOTED_IDENTIFIER 옵션이 ON이어야 합니다. 이 값은 기본 설정입니다. 자세한 내용은 SET QUOTED_IDENTIFIER(Transact-SQL)를 참조하십시오.

  • [PRIMARY] XML
    지정된 xml 열에 XML 인덱스를 만듭니다. PRIMARY를 지정한 경우 사용자 테이블의 클러스터링 키 및 XML 노드 식별자로 구성된 클러스터형 키를 사용하여 클러스터형 인덱스가 만들어집니다. 각 테이블에는 최대 249개의 XML 인덱스가 있을 수 있습니다. XML 인덱스를 만들 때는 다음 사항을 알아야 합니다.

    • 클러스터형 인덱스는 사용자 테이블의 기본 키에 있어야 합니다.
    • 사용자 테이블의 클러스터링 키는 15개의 열로 제한됩니다.
    • 테이블의 각 xml 열에는 하나의 기본 XML 인덱스 및 여러 개의 보조 XML 인덱스가 있을 수 있습니다.
    • xml 열에 기본 XML 인덱스가 있어야 이 열에 보조 XML 인덱스를 만들 수 있습니다.
    • XML 인덱스는 단일 xml 열에만 만들 수 있습니다. 비-xml 열에 XML 인덱스를 만들 수 없거나 xml 열에 관계형 인덱스를 만들 수 없습니다.
    • 뷰의 xml 열, xml 열이 있는 테이블 값 변수 또는 xml 유형 변수에 기본 XML 인덱스 또는 보조 XML 인덱스를 만들 수 없습니다.
    • 계산 xml 열에 기본 XML 인덱스를 만들 수 없습니다.
    • SET 옵션 설정은 인덱싱된 뷰 및 계산 열 인덱스에 필요한 설정과 같아야 합니다. 특히 XML 인덱스를 만들 때 및 xml 열에서 값을 삽입, 삭제 또는 업데이트할 때 ARITHABORT 옵션은 ON으로 설정되어 있어야 합니다. 자세한 내용은 결과에 영향을 주는 SET 옵션을 참조하십시오.

    자세한 내용은 xml 데이터 형식 열의 인덱스를 참조하십시오.

  • xml_column_name
    인덱스가 기반이 되는 xml 열입니다. 단일 XML 인덱스 정의에서는 하나의 xml 열만 지정할 수 있지만 xml 열에서는 여러 개의 보조 XML 인덱스를 만들 수 있습니다.
  • USING XML INDEX xml_index_name
    보조 XML 인덱스를 만들 때 사용할 기본 XML 인덱스를 지정합니다.

  • FOR { VALUE | PATH | PROPERTY }
    보조 XML 인덱스의 유형을 지정합니다.

    • VALUE
      키 열이 기본 XML 인덱스의 노드 값 및 경로인 열에 보조 XML 인덱스를 만듭니다.
    • PATH
      기본 XML 인덱스의 경로 값 및 노드 값에 작성된 열에 보조 XML 인덱스를 만듭니다. PATH 보조 인덱스에서 경로 값 및 노드 값은 경로 검색 시 효율적으로 검색할 수 있는 키 열입니다.
    • PROPERTY
      PK가 기본 테이블의 기본 키인 기본 XML 인덱스의 열(PK, 경로 및 노드 값)에 보조 XML 인덱스를 만듭니다.
<object>::=

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

  • database_name
    데이터베이스의 이름입니다.
  • schema_name
    테이블이나 뷰가 속한 스키마의 이름입니다.

  • table_or_view_name
    인덱스할 테이블 또는 뷰의 이름입니다.

    인덱스를 만들 뷰는 SCHEMABINDING로 정의되어야 합니다. 뷰에 비클러스터형 인덱스를 만들려면 먼저 고유 클러스터형 인덱스를 만들어야 합니다. 인덱싱된 뷰에 대한 자세한 내용은 주의 섹션을 참조하십시오.

<relational_index_option>::=

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

  • PAD_INDEX = { ON | OFF }
    인덱스 패딩을 지정합니다. 기본값은 OFF입니다.

    • ON
      fillfactor로 지정된 사용 가능한 공간의 비율이 인덱스의 중간 수준 페이지에 적용됩니다.
    • OFF 또는 fillfactor가 지정되지 않았습니다.
      중간 수준 페이지는 중간 페이지의 키 집합을 고려하며 인덱스가 가질 수 있는 최대 크기의 한 행에 필요한 공간을 충분히 남기고 용량을 거의 채웁니다.

    PAD_INDEX는 FILLFACTOR에 지정된 비율을 사용하므로 FILLFACTOR가 지정된 경우에만 PAD_INDEX 옵션을 사용할 수 있습니다. FILLFACTOR에 지정된 비율이 한 행을 저장하기에도 부족하면 데이터베이스 엔진은 내부적으로 허용된 최소 비율을 무시합니다. fillfactor 값이 아무리 작더라도 중간 인덱스 페이지의 행 수는 두 개 이상입니다.

    이전 버전과 호환되는 구문에서 WITH PAD_INDEX는 WITH PAD_INDEX = ON과 같습니다.

  • FILLFACTOR **=**fillfactor
    인덱스를 만들거나 다시 작성하는 동안 데이터베이스 엔진에서 각 인덱스 페이지의 리프 수준을 어느 정도 채울지 나타내는 비율을 지정합니다. fillfactor는 1에서 100 사이의 정수 값이어야 하며 기본값은 0입니다. fillfactor가 100 또는 0인 경우 데이터베이스 엔진에서는 리프 페이지가 꽉 찬 인덱스를 만듭니다.

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

    FILLFACTOR 설정은 인덱스를 만들거나 다시 작성하는 경우에만 적용됩니다. 데이터베이스 엔진은 페이지의 빈 공간에 대해 지정된 비율을 동적으로 유지하지 않습니다. 채우기 비율 설정을 보려면 sys.indexes 카탈로그 뷰를 사용하십시오.

    ms188783.note(ko-kr,SQL.90).gif중요:
    데이터베이스 엔진은 클러스터형 인덱스를 만들 때 데이터를 다시 배포하므로 FILLFACTOR가 100 미만인 클러스터형 인덱스를 만들면 데이터가 차지하는 저장 공간 크기에 영향을 줍니다.

    자세한 내용은 채우기 비율을 참조하십시오.

  • SORT_IN_TEMPDB = { ON | OFF }
    tempdb에 임시 정렬 결과를 저장할지 여부를 지정합니다. 기본값은 OFF입니다.

    • ON
      인덱스 작성에 사용된 중간 정렬 결과가 tempdb에 저장됩니다. 따라서 tempdb가 사용자 데이터베이스 대신 다른 디스크 세트에 있는 경우 인덱스를 만드는 시간을 단축할 수 있습니다. 그러나 이렇게 하면 인덱스를 작성하는 동안 사용되는 디스크 공간의 크기가 늘어납니다.
    • OFF
      중간 정렬 결과는 인덱스처럼 동일한 데이터베이스에 저장됩니다.

    사용자 데이터베이스에서 인덱스를 만드는 데 필요한 공간 외에 tempdb에는 중간 정렬 결과를 저장할 정도의 동일한 공간이 추가로 필요합니다. 자세한 내용은 tempdb 및 인덱스 만들기를 참조하십시오.

    이전 버전과 호환되는 구문에서 WITH SORT_IN_TEMPDB는 WITH SORT_IN_TEMPDB = ON과 같습니다.

  • IGNORE_DUP_KEY = { ON | OFF }
    고유 클러스터형 또는 고유 비클러스터형 인덱스에 여러 행을 삽입하는 작업에서 중복된 키 값이 있는 경우에 대한 오류 응답을 지정합니다. 기본값은 OFF입니다.

    • ON
      경고 메시지가 발생하고 고유 인덱스를 위반하는 행만 실패합니다.
    • OFF
      오류 메시지가 발생하고 전체 INSERT 트랜잭션이 롤백됩니다.

    IGNORE_DUP_KEY 설정은 인덱스를 만들거나 다시 작성한 후에 수행되는 삽입 작업에만 적용됩니다. 인덱스 생성 작업 중에는 이 설정이 적용되지 않습니다.

    XML 인덱스 및 뷰에 만든 인덱스의 경우 IGNORE_DUP_KEY를 ON으로 설정할 수 없습니다.

    이전 버전과 호환되는 구문에서 WITH IGNORE_DUP_KEY는 WITH IGNORE_DUP_KEY = ON과 같습니다.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    배포 통계를 다시 계산할지 여부를 지정합니다. 기본값은 OFF입니다.

    • ON
      이전 통계는 자동으로 다시 계산되지 않습니다.
    • OFF
      자동 통계 업데이트가 설정됩니다.

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

    ms188783.note(ko-kr,SQL.90).gif중요:
    배포 통계의 자동 재계산 기능을 해제하면 쿼리 최적화 프로그램에서 테이블과 관련된 쿼리에 맞는 최적의 실행 계획을 선택할 수 없습니다.

    이전 버전과 호환되는 구문에서 WITH STATISTICS_NORECOMPUTE는 WITH STATISTICS_NORECOMPUTE = ON과 같습니다.

  • DROP_EXISTING = { ON | OFF }
    명명된 기존 클러스터형, 비클러스터형 또는 XML 인덱스를 삭제하고 다시 작성하도록 지정합니다. 기본값은 OFF입니다.

    • ON
      기존 인덱스가 삭제되고 다시 작성됩니다. 지정된 인덱스 이름은 현재 존재하는 인덱스 이름과 같아야 합니다. 그러나 인덱스 정의는 수정할 수 있습니다. 예를 들어 다른 열, 정렬 순서, 파티션 구성표 또는 인덱스 옵션을 지정할 수 있습니다.
    • OFF
      지정된 인덱스 이름이 이미 존재하는 경우 오류가 표시됩니다.

    관계형 또는 XML의 인덱스 유형은 DROP_EXISTING을 사용하여 변경할 수 없습니다. 또한 기본 XML 인덱스는 보조 XML 인덱스로 다시 정의할 수 없으며 반대인 경우도 마찬가지입니다.

    이전 버전과 호환되는 구문에서 WITH DROP_EXISTING은 WITH DROP_EXISTING = ON과 같습니다.

  • ONLINE = { ON | OFF }
    인덱스 작업 중 쿼리 및 데이터 수정을 위해 기본 테이블 및 관련된 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다.

    [!참고] 온라인 인덱스 작업은 SQL Server 2005 Enterprise Edition에서만 사용할 수 있습니다.

    • ON
      인덱스 작업 기간 동안 장기간용 테이블 잠금이 보유되지 않습니다. 인덱스 작업의 주 단계를 수행하는 동안 IS(의도 공유) 잠금만 원본 테이블에 보유됩니다. 따라서 기본 테이블 및 인덱스에 대한 쿼리나 업데이트를 처리할 수 있습니다. 작업이 시작되면 단기간 동안 S(공유) 잠금이 원본 개체에 보유됩니다. 작업이 끝나면 단기간 동안 비클러스터형 인덱스를 만드는 경우 원본에 대해 S(공유) 잠금이 획득되며 온라인 상태에서 클러스터형 인덱스를 만들거나 삭제하는 경우 및 클러스터형 또는 비클러스터형 인덱스를 다시 작성하는 경우 SCH-M(스키마 수정) 잠금이 획득됩니다. 로컬 임시 테이블에서 인덱스를 만드는 경우에는 ONLINE을 ON으로 설정할 수 없습니다.
    • OFF
      인덱스 작업 기간 동안 테이블 잠금이 적용됩니다. 클러스터형 인덱스를 생성, 다시 작성 또는 삭제하거나 비클러스터형 인덱스를 다시 작성 또는 삭제하는 오프라인 인덱스 작업을 통해 테이블의 SCH-M(스키마 수정) 잠금을 획득합니다. 이 경우 작업 기간 동안 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다. 비클러스터형 인덱스를 만드는 오프라인 인덱스 작업을 통해 테이블의 S(공유) 잠금을 획득합니다. 따라서 기본 테이블을 업데이트할 수 없지만 SELECT 문과 같은 읽기 작업은 허용됩니다.

    자세한 내용은 온라인 인덱스 작동 방식을 참조하십시오. 잠금에 대한 자세한 내용은 잠금 모드를 참조하십시오.

    전역 임시 테이블의 인덱스를 포함한 인덱스는 다음 예외의 경우를 제외하고 온라인 상태로 만들 수 있습니다.

    • XML 인덱스

    • 로컬 임시 테이블의 인덱스

    • 뷰의 초기 고유 클러스터형 인덱스

    • 해제된 클러스터형 인덱스

    • 기본 테이블에 image, ntext, text, varchar(max), nvarchar(max), varbinary(max) 및 xml의 LOB 데이터 형식이 들어 있는 경우 클러스터형 인덱스

    • LOB 데이터 형식 열로 정의된 비클러스터형 인덱스

      [!참고] 테이블에 LOB 데이터 형식이 들어 있는 경우 비고유 비클러스터형 인덱스를 온라인 상태로 만들 수 있지만 이러한 열은 모두 인덱스 정의에 키 또는 키가 아닌 포괄 열로 사용되지 않습니다.

    자세한 내용은 온라인으로 인덱스 작업 수행을 참조하십시오.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    행 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

    • ON
      인덱스에 액세스할 때 행 잠금이 허용됩니다. 데이터베이스 엔진은 행 잠금이 사용될 시기를 결정합니다.
    • OFF
      행 잠금이 사용되지 않습니다.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    페이지 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

    • ON
      인덱스에 액세스할 때 페이지 잠금이 허용됩니다. 데이터베이스 엔진은 페이지 잠금이 사용될 시기를 결정합니다.
    • OFF
      페이지 잠금이 사용되지 않습니다.

  • MAXDOP = max_degree_of_parallelism
    인덱스 작업 기간 동안 최대 병렬 처리 수준 구성 옵션을 재정의합니다. MAXDOP를 사용하여 병렬 계획 실행에 사용되는 프로세서 수를 제한할 수 있습니다. 프로세서의 최대값은 64개입니다.

    max_degree_of_parallelism은 다음 값이 될 수 있습니다.

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

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

    [!참고] 병렬 인덱스 작업은 SQL Server 2005 Enterprise Edition에서만 사용할 수 있습니다.

주의

CREATE INDEX 문은 다른 쿼리와 마찬가지로 최적화됩니다. I/O 작업을 줄이려면 쿼리 프로세서는 테이블 스캔을 수행하는 대신 다른 인덱스를 스캔하도록 선택할 수도 있습니다. 정렬 작업을 제거해야 하는 경우도 있습니다. SQL Server 2005 Enterprise Edition을 사용하는 다중 프로세서 컴퓨터에서 CREATE INDEX는 다른 쿼리에서 수행하는 것과 같은 방법으로 더 많은 프로세서를 사용하여 인덱스 만들기와 관련된 스캔 및 정렬 작업을 수행할 수 있습니다. 자세한 내용은 병렬 인덱스 작업 구성을 참조하십시오.

데이터베이스 복구 모델이 대량 로그 또는 단순으로 설정된 경우 인덱스 생성 작업은 최소로 로그될 수 있습니다. 자세한 내용은 인덱스 작업에 대한 복구 모델 선택을 참조하십시오.

임시 테이블에 인덱스를 만들 수 있습니다. 테이블을 삭제하거나 세션이 종료되면 인덱스가 삭제됩니다.

인덱스는 확장 속성을 지원합니다. 자세한 내용은 데이터베이스 개체의 확장 속성 사용을 참조하십시오.

클러스터형 인덱스

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

고유 인덱스

고유 인덱스가 있으면 데이터베이스 엔진은 삽입 작업으로 데이터를 추가할 때마다 중복 값이 있는지 확인합니다. 중복 키 값을 생성하는 삽입 작업은 롤백되며 데이터베이스 엔진에서는 오류 메시지를 표시합니다. 이런 현상은 삽입 작업을 통해 많은 행을 변경할 때 중복 값이 하나만 있어도 발생합니다. 고유 인덱스가 있고 IGNORE_DUP_KEY 절이 ON으로 설정된 경우 데이터를 입력하려고 하면 UNIQUE 인덱스를 위반하는 행만 실패합니다. 고유 인덱스에 대한 자세한 내용은 고유 인덱스 만들기를 참조하십시오.

분할된 인덱스

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

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

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

인덱싱된 뷰는 테이블의 인덱스와 같은 방법으로 분할된 테이블에 만들 수 있습니다. 분할된 인덱스에 대한 자세한 내용은 분할된 테이블 및 인덱스를 참조하십시오.

인덱싱된 뷰

뷰에 고유 클러스터형 인덱스를 만들면 클러스터형 인덱스가 있는 테이블의 저장 방식과 마찬가지로 데이터베이스에 뷰가 저장되므로 쿼리 성능이 향상됩니다. 쿼리 최적화 프로그램은 인덱싱된 뷰를 사용하여 쿼리 실행 속도를 높일 수 있습니다. 최적화 프로그램이 인덱싱된 뷰를 대신 사용하므로 쿼리에서 해당 뷰를 참조할 필요가 없습니다.

다음 단계는 인덱싱된 뷰를 만들고 뷰를 성공적으로 구현하는 데 필요합니다.

  1. 뷰에 참조될 기존의 모든 테이블에 대해 SET 옵션이 올바른지 확인합니다.
  2. 새 테이블 및 뷰를 만들기 전에 세션에 SET 옵션이 올바르게 설정되어 있는지 확인합니다.
  3. 뷰 정의가 결정적인지 확인합니다.
  4. WITH SCHEMABINDING 옵션을 사용하여 뷰를 만듭니다.
  5. 뷰에 고유 클러스터형 인덱스를 만듭니다.

인덱싱된 뷰에 필요한 SET 옵션

쿼리가 실행될 때 다른 SET 옵션이 활성화되어 있으면 같은 식을 계산해도 데이터베이스 엔진에서 다른 결과가 나올 수 있습니다. 예를 들어 SET 옵션 CONCAT_NULL_YIELDS_NULL이 ON으로 설정된 후 식 'abc' + NULL의 결과로 NULL 값이 반환됩니다. 그러나 CONCAT_NULL_YIEDS_NULL을 OFF로 설정한 후 같은 식의 결과는 **'abc'**가 됩니다.

뷰를 올바르게 유지하고 일관된 결과를 반환하게 하려면 인덱싱된 뷰는 몇 가지 SET 옵션에 대해 고정 값이 필요합니다. 다음 테이블의 SET 옵션은 다음 상황이 발생할 때마다 필요한값 열에 표시된 값으로 설정되어야 합니다.

  • 인덱싱된 뷰를 만듭니다.

  • 인덱싱된 뷰에 참가하는 테이블에서 삽입, 업데이트 또는 삭제 작업이 수행됩니다. 여기에는 대량 복사, 복제, 분산 쿼리 등의 작업이 포함됩니다.

  • 쿼리 최적화 프로그램에서 인덱싱된 뷰를 사용하여 쿼리 계획을 만듭니다.

    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

    *SQL Server 2005에서 ANSI_WARNINGS를 ON으로 설정하면 데이터베이스 호환성 수준이 90으로 설정된 경우 암시적으로 ARITHABORT가 ON으로 설정됩니다. 데이터베이스 호환성 수준이 80 이전으로 설정된 경우에는 ARITHABORT 옵션을 명시적으로 ON으로 설정해야 합니다.

OLE DB 또는 ODBC 서버 연결을 사용하는 경우 수정해야 하는 유일한 값은 ARITHABORT 설정입니다. 모든 DB-Library 값은 sp_configure를 사용하여 서버 수준에서 또는 SET 명령을 사용하여 응용 프로그램에서 올바르게 설정해야 합니다. SET 옵션에 대한 자세한 내용은 SQL Server에서 옵션 사용을 참조하십시오.

ms188783.note(ko-kr,SQL.90).gif중요:
서버의 데이터베이스에서 계산 열에 첫 번째로 인덱싱된 뷰 또는 인덱스가 만들어지면 바로 서버 차원에서 ARITHABORT 사용자 옵션을 ON으로 설정하는 것이 좋습니다.

결정적 함수

인덱싱된 뷰의 정의는 결정적이어야 합니다. WHERE 및 GROUP BY 절 뿐만 아니라 선택 목록의 모든 식이 결정적이면 뷰가 결정적입니다. 결정적 식은 특정 입력 값 집합을 사용하여 계산될 때마다 항상 같은 결과를 반환합니다. 결정적 함수만 결정적 식에 참여할 수 있습니다. 예를 들어 DATEADD 함수는 세 매개 변수에 지정된 인수 값 집합에 대해 항상 같은 결과를 반환하므로 결정적 함수입니다. GETDATE는 항상 같은 인수로 호출되지만 실행될 때마다 반환하는 값이 바뀌므로 비결정적 함수입니다. 자세한 내용은 결정적 함수 및 비결정적 함수를 참조하십시오.

식이 결정적인 경우에도 float 식이 포함되어 있으면 정확한 결과는 프로세서 아키텍처 또는 마이크로코드 버전에 따라 달라질 수 있습니다. 데이터 무결성을 보장하기 위해 이런 식은 인덱싱된 뷰의 키가 아닌 열로만 참여할 수 있습니다. float 식이 없는 결정적 식을 정확하다고 합니다. 정확한 결정적 식만 인덱싱된 뷰의 WHERE 또는 GROUP BY 절과 키 열에 참여할 수 있습니다.

COLUMNPROPERTY 함수의 IsDeterministic 속성을 사용하여 뷰 열이 결정적인지 여부를 확인할 수 있습니다. COLUMNPROPERTY 함수의 IsPrecise 속성을 사용하여 스키마 바인딩되어 있는 뷰의 결정적 열이 정확한지 여부를 결정할 수 있습니다. COLUMNPROPERTY는 TRUE이면 1을, FALSE이면 0을 반환하며 입력이 잘못되면 NULL을 반환합니다. 이는 해당 열이 비결정적이거나 정확하지 않음을 의미합니다.

추가 요구 사항

SET 옵션 및 결정적 함수 요구 사항 외에 다음 요구 사항을 충족해야 합니다.

  • CREATE INDEX를 실행하는 사용자는 뷰의 소유자여야 합니다.

  • 뷰 정의에 GROUP BY 절이 들어 있으면 고유 클러스터형 인덱스의 키는 GROUP BY 절에 지정된 열만 참조할 수 있습니다.

  • 기본 테이블은 생성 시 올바른 SET 옵션을 설정해야 합니다. 그렇지 않으면 스키마 바인딩되어 있는 뷰에서 테이블을 참조할 수 없습니다.

  • 테이블은 뷰 정의에서 schema**.**tablename처럼 두 부분으로 구성된 이름으로 참조되어야 합니다.

  • 사용자 정의 함수는 WITH SCHEMABINDING 옵션을 사용하여 만들어야 합니다.

  • 사용자 정의 함수는 schema**.**function처럼 두 부분으로 구성된 이름으로 참조되어야 합니다.

  • 뷰는 WITH SCHEMABINDING 옵션을 사용하여 만들어야 합니다.

  • 뷰는 다른 뷰가 아닌 동일한 데이터베이스의 기본 테이블만 참조해야 합니다.

  • 뷰 정의에 다음 사항을 포함할 수 없습니다.

    COUNT(*)

    ROWSET 함수

    파생 테이블

    자체 조인

    DISTINCT

    STDEV, VARIANCE, AVG

    float*, text, ntext 또는 image 열

    하위 쿼리

    전체 텍스트 조건자(CONTAIN, FREETEXT)

    Null 허용 식의 SUM

    CLR 사용자 정의 집계 함수

    TOP

    MIN, MAX

    UNION

    *인덱싱된 뷰는 float 열을 포함할 수 있지만 이러한 열은 클러스터형 인덱스 키에 포함될 수 없습니다.

GROUP BY가 있는 경우 VIEW 정의는 COUNT_BIG(*)을 포함해야 하며 HAVING은 포함할 수 없습니다. 이러한 GROUP BY 제약 조건은 인덱싱된 뷰 정의에만 적용됩니다. 쿼리는 이러한 GROUP BY 제약 조건을 충족하지 않는 경우에도 실행 계획에 인덱싱된 뷰를 사용할 수 있습니다.

인덱싱된 뷰는 분할된 테이블에서 만들 수 있으며 자신이 분할될 수 있습니다. 분할에 대한 자세한 내용은 이전 섹션 "분할된 인덱스"를 참조하십시오.

데이터베이스 엔진에서 인덱싱된 뷰를 사용하지 않게 하려면 쿼리에 OPTION (EXPAND VIEW) 힌트를 포함합니다. 또한 위에 표시된 옵션을 하나라도 잘못 설정하면 최적화 프로그램에서 뷰의 인덱스를 사용할 수 없게 됩니다. OPTION (EXPAND VIEW) 힌트에 대한 자세한 내용은 SELECT(Transact-SQL)를 참조하십시오.

데이터베이스의 호환성 수준은 80보다 적을 수 없습니다. 인덱싱된 뷰가 들어 있는 데이터베이스의 호환성 수준을 80 미만으로 변경할 수 없습니다.

XML 인덱스

자세한 내용은 xml 데이터 형식 열의 인덱스를 참조하십시오.

인덱스 키 크기

인덱스 키의 최대 크기는 900바이트입니다. 900바이트를 초과하는 varchar 열에 있는 기존 데이터가 900바이트를 초과하지 않는 경우 이 열에 900바이트를 초과하는 인덱스를 만들 수 있습니다. 그러나 전체 크기가 900바이트를 초과하는 열에서 다음 삽입 작업이나 업데이트 작업이 실패합니다. 자세한 내용은 인덱스 키의 최대 크기를 참조하십시오. 클러스터형 인덱스의 인덱스 키는 기존 데이터가 ROW_OVERFLOW_DATA 할당 단위에 있는 varchar 열을 포함할 수 없습니다. 클러스터형 인덱스를 varchar 열에 만들고 기존 데이터가 IN_ROW_DATA 할당 단위에 있는 경우에는 데이터를 행 외부로 밀어넣을 열에서 다음 삽입 작업이나 업데이트 작업이 실패합니다. 할당 단위에 대한 자세한 내용은 테이블 및 인덱스 구성을 참조하십시오.

SQL Server 2005에서 비클러스터형 인덱스는 인덱스의 리프 수준에 키가 아닌 열을 포함할 수 있습니다. 인덱스 키 크기를 계산할 때 데이터베이스 엔진은 이러한 열을 계산하지 않습니다. 자세한 내용은 포괄 열이 있는 인덱스를 참조하십시오.

계산 열

계산 열에 인덱스를 만들 수 있습니다. SQL Server 2005에서 계산 열은 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 2005에는 새 인덱스 옵션이 추가되었으며 옵션 지정 방법도 수정했습니다. 이전 버전과 호환되는 구문에서 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 절을 사용하여 인덱스 다시 작성, 열 추가 또는 삭제, 옵션 수정, 열 정렬 순서 수정 또는 파티션 구성표나 파일 그룹 변경 등의 작업을 수행할 수 있습니다.

인덱스가 PRIMARY KEY 또는 UNIQUE 제약 조건을 강제 적용하고 인덱스 정의가 변경되지 않으면 이 인덱스는 삭제되고 기존 제약 조건을 보존한 상태에서 다시 만들어집니다. 그러나 인덱스 정의가 변경되면 이 문은 실패합니다. PRIMARY KEY 또는 UNIQUE 제약 조건의 정의를 변경하려면 제약 조건을 삭제하고 새 정의가 있는 제약 조건을 추가합니다.

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

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

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

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

ONLINE 옵션

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

  • 온라인 인덱스 작업이 진행되는 동안에는 기본 테이블을 변경하거나 자르거나 삭제할 수 없습니다.
  • 인덱스 작업 중에 임시 디스크 공간이 추가로 필요합니다. 자세한 내용은 인덱스 디스크 공간 요구 사항 확인을 참조하십시오.
  • 온라인 작업은 분할된 인덱스 및 지속형 계산 열이 들어 있는 인덱스 또는 포괄 열에서 수행될 수 있습니다.

자세한 내용은 온라인으로 인덱스 작업 수행을 참조하십시오.

행 및 페이지 잠금 옵션

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

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

인덱스에 대한 잠금 세분성을 구성하는 방법은 인덱스 잠금 사용자 지정을 참조하십시오.

인덱스 정보 보기

인덱스 정보를 반환하려면 카탈로그 뷰, 시스템 함수 및 시스템 저장 프로시저를 사용할 수 있습니다. 자세한 내용은 인덱스 정보 보기를 참조하십시오.

사용 권한

테이블이나 뷰에 대한 ALTER 권한이 필요합니다. 사용자는 sysadmin 고정 서버 역할의 멤버 또는 db_ddladmin 및 db_owner 고정 데이터베이스 역할의 멤버여야 합니다.

예

1. 단순 비클러스터형 인덱스 만들기

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

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (VendorID); 
GO

2. 단순 비클러스터형 복합 인덱스 만들기

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

USE AdventureWorks
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

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

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

USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

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

--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.

4. IGNORE_DUP_KEY 옵션 사용

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

USE AdventureWorks;
GO
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로 설정한 상태입니다.

USE AdventureWorks;
GO
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 인덱스 제약 조건을 위반했지만 이 테이블에서 어떤 행도 삽입되지 않았습니다.

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

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

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

6. 뷰에 인덱스 만들기

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

USE AdventureWorks;
GO
--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

7. 키가 아닌 포괄 열이 있는 인덱스 만들기

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

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
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

8. 기본 XML 인덱스 만들기

다음은 Production.ProductModel 테이블의 CatalogDescription 열에 기본 XML 인덱스를 만드는 예입니다.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

9. 보조 XML 인덱스 만들기

다음은 Production.ProductModel 테이블의 CatalogDescription 열에 보조 XML 인덱스를 만드는 예입니다.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

10. 분할된 인덱스 만들기

다음은 기존 파티션 구성표인 TransactionsPS1에 분할된 비클러스터형 인덱스를 만드는 예입니다. 이 예에서는 분할된 인덱스 샘플이 설치되었다고 가정합니다. 설치 정보를 보려면 Readme_PartitioningScript를 참조하십시오.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_TransactionHistory_ReferenceOrderID')
    DROP INDEX IX_TransactionHistory_ReferenceOrderID
        ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO

참고 항목

참조

ALTER INDEX(Transact-SQL)
CREATE PARTITION FUNCTION(Transact-SQL)
CREATE PARTITION SCHEME(Transact-SQL)
CREATE STATISTICS(Transact-SQL)
CREATE TABLE(Transact-SQL)
데이터 형식(Transact-SQL)
DBCC SHOW_STATISTICS(Transact-SQL)
DROP INDEX(Transact-SQL)
sys.indexes(Transact-SQL)
sys.index_columns(Transact SQL)
sys.xml_indexes(Transact-SQL)
EVENTDATA(Transact-SQL)

관련 자료

인덱스 디스크 공간 요구 사항 확인
일반 인덱스 디자인 지침
xml 데이터 형식 열의 인덱스
테이블 및 인덱스 아키텍처

도움말 및 정보

SQL Server 2005 지원 받기

변경 내역

릴리스 내역

2006년 4월 14일
업데이트된 내용
  • 온라인으로 만들 수 없는 인덱스 목록에서 고유 비클러스터형 인덱스를 제거했습니다. 이 변경 내용은 SQL Server 2005 서비스 팩 1 이상에 적용됩니다.
  • ANSI_WARNINGS를 ON으로 설정한 경우 ARITHABORT 설정에 미치는 영향에 대한 내용을 SET 옵션 표에 각주로 추가했습니다.

2005년 12월 5일
새로운 내용
  • 온라인으로 만들 수 없는 인덱스 목록에 고유 비클러스터형 인덱스를 추가했습니다.