CREATE XML INDEX(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

지정된 테이블에 XML 인덱스를 만듭니다. 인덱스는 테이블에 데이터를 넣기 전에 만들 수 있습니다. 정규화된 데이터베이스 이름을 지정하여 다른 데이터베이스에 있는 테이블에 XML 인덱스를 만들 수도 있습니다.

참고

관계형 인덱스를 만들려면 CREATE INDEX(Transact-SQL)를 참조하세요. 공간 인덱스를 만드는 방법은 CREATE SPATIAL INDEX(Transact-SQL)를 참조하세요.

Transact-SQL 구문 표기 규칙

구문

--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.table_name | schema_name.table_name | table_name }

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

인수

[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으로 설정되어 있어야 합니다.

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

index_name

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

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

xml_column_name

인덱스의 기반이 되는 xml 열입니다. 단일 XML 인덱스 정의에서는 하나의 xml 열만 지정할 수 있지만 xml 열에서는 여러 개의 보조 XML 인덱스를 만들 수 있습니다.

USING XML INDEX xml_index_name

보조 XML 인덱스를 만들 때 사용할 기본 XML 인덱스를 지정합니다.

FOR { VALUE | PATH | PROPERTY }

보조 XML 인덱스의 유형을 지정합니다.

값
키 열이 기본 XML 인덱스의 노드 값 및 경로인 열에 보조 XML 인덱스를 만듭니다.

PATH
기본 XML 인덱스의 경로 값 및 노드 값에 작성된 열에 보조 XML 인덱스를 만듭니다. PATH 보조 인덱스에서 경로 값 및 노드 값은 경로 검색 시 효율적으로 검색할 수 있는 키 열입니다.

PROPERTY
PK가 기본 테이블의 기본 키인 기본 XML 인덱스의 열(PK, 경로 및 노드 값)에 보조 XML 인덱스를 만듭니다.

<object>::=

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

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

schema_name
테이블이 속한 스키마의 이름입니다.

table_name
인덱싱할 테이블 이름입니다.

<xml_index_option> ::=

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

PAD_INDEX = { ON | OFF }

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

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

OFF 또는 fillfactor를 지정하지 않음
중간 수준 페이지는 중간 페이지의 키 집합을 고려하며 인덱스가 가질 수 있는 최대 크기의 한 행에 필요한 공간을 충분히 남기고 용량을 거의 채웁니다.

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

FILLFACTOR = fillfactor

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

참고

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

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

중요

데이터베이스 엔진은 클러스터형 인덱스를 만들 때 데이터를 다시 배포하므로 FILLFACTOR가 100 미만인 클러스터형 인덱스를 만들면 데이터가 차지하는 스토리지 공간 크기에 영향을 줍니다.

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

SORT_IN_TEMPDB = { ON | OFF }

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

켜기
인덱스 작성에 사용된 중간 정렬 결과가 tempdb에 저장됩니다. 이 경우 사용자 데이터베이스가 아닌 다른 디스크 집합에 tempdb가 있으면 인덱스 생성에 필요한 시간이 단축될 수 있습니다. 그러나 인덱스 작성 중에 사용되는 디스크 공간의 크기는 커집니다.

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

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

IGNORE_DUP_KEY = OFF

인덱스 유형은 고유할 수 없으므로 XML 인덱스에 영향을 미치지 않습니다. 이 옵션을 ON으로 설정하지 마세요. ON으로 설정하면 오류가 발생합니다.

DROP_EXISTING = { ON | OFF }

명명된 기존 XML 인덱스를 삭제하고 다시 작성하도록 지정합니다. 기본값은 OFF입니다.

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

OFF
지정된 인덱스 이름이 이미 존재하는 경우 오류가 표시됩니다.

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

ONLINE = OFF

인덱스 작업 중에 쿼리 및 데이터 수정에 기본 테이블 및 관련 인덱스를 사용할 수 없도록 지정합니다. 이 SQL Server 버전에서는 XML 인덱스에 대해 온라인 인덱스 작성이 지원되지 않습니다. 이 옵션이 XML 인덱스에 대해 ON으로 설정되어 있으면 오류가 발생합니다. ONLINE 옵션을 생략하거나 ONLINE을 OFF로 설정하세요.

XML 인덱스를 작성, 다시 작성 또는 삭제하는 오프라인 인덱스 작업은 테이블에 대해 Sch-M(스키마 수정) 잠금을 획득합니다. 이렇게 하면 작업 중에 모든 사용자가 기본 테이블에 액세스할 수 없게 됩니다.

참고

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

ALLOW_ROW_LOCKS = { ON | OFF }

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

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

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

ALLOW_PAGE_LOCKS = { ON | OFF }

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

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

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

MAXDOP = max_degree_of_parallelism

인덱스 작업 동안 max degree of parallelism 서버 구성 옵션 구성 구성 옵션을 재정의합니다. MAXDOP를 사용하여 병렬 계획 실행에 사용되는 프로세서 수를 제한할 수 있습니다. 최대값은 64개입니다.

중요

MAXDOP 옵션은 모든 XML 인덱스에 대해 구문으로는 지원되지만 기본 XML 인덱스의 경우 CREATE XML INDEX는 단일 프로세서만 사용합니다.

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

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

>1
병렬 인덱스 작업에 사용되는 최대 프로세서 수를 현재 시스템 작업에 따라 지정된 수 또는 더 적은 수로 제한합니다.

0(기본값)
현재 시스템 작업에 따라 실제 프로세서 수 이하의 프로세서를 사용합니다.

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

참고

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

설명

xml 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 인덱스 키 열 또는 키가 아닌 열에 허용되는 경우에 한해 키 또는 키가 아닌 포괄 열로 인덱싱될 수 있습니다. 계산된 xml 열에 기본 XML 인덱스를 만들 수 없습니다.

XML 인덱스에 대한 정보를 보려면 sys.xml_indexes 카탈로그 뷰를 사용합니다.

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

XML 압축

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

• XML 인덱스는 테이블의 압축 속성을 상속하지 않습니다. 인덱스를 압축하려면 XML 인덱스에 대해 XML 압축을 명시적으로 사용하도록 설정해야 합니다.
• 보조 XML 인덱스는 기본 XML 인덱스의 압축 속성을 상속하지 않습니다.
• 기본적으로 인덱스를 만들 때 XML 인덱스의 XML 압축 설정은 OFF로 설정됩니다.

인덱스 작성에 대한 추가 주의 사항

인덱스 작성에 대한 자세한 내용은 CREATE INDEX(Transact-SQL)에서 “주의 사항” 섹션을 참조하세요.

예제

이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

A. 기본 XML 인덱스 만들기

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

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

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

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

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

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)
    WITH (XML_COMPRESSION = ON);  
GO

C. 보조 XML 인덱스 만들기

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

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

D. XML 압축을 사용하여 보조 XML 인덱스 만들기

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

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

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
    WITH (XML_COMPRESSION = ON);
GO

추가 정보

• ALTER INDEX(Transact-SQL)
• CREATE INDEX(Transact-SQL)
• CREATE PARTITION FUNCTION(Transact-SQL)
• CREATE PARTITION SCHEME(Transact-SQL)
• CREATE SPATIAL INDEX(Transact-SQL)
• CREATE STATISTICS(Transact-SQL)
• CREATE TABLE(Transact-SQL)
• 데이터 형식(Transact-SQL)
• DBCC SHOW_STATISTICS(Transact-SQL)
• DROP INDEX(Transact-SQL)
• XML 인덱스(SQL Server)
• sys.indexes(Transact-SQL)
• sys.index_columns(Transact SQL)
• sys.xml_indexes(Transact-SQL)
• EVENTDATA(Transact-SQL)
• XML 인덱스(SQL Server)