다음을 통해 공유

XML 인덱스 만들기

  • 아티클

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance

이 문서에서는 기본 및 보조 XML 인덱스를 만드는 방법에 대해 설명합니다.

기본 XML 인덱스 만들기

기본 XML 인덱스를 만들려면 CREATE INDEX (Transact-SQL)Transact-SQL DDL 문을 사용합니다. 비 XML 인덱스에 사용할 수 있는 모든 옵션이 XML 인덱스에 지원되는 것은 아닙니다.

XML 인덱스를 만들 때는 다음에 유의해야 합니다.

  • 기본 XML 인덱스를 만들려면 기본 테이블이라는 인덱싱되는 XML 열이 포함된 테이블에 기본 키에서 클러스터형 인덱스가 있어야 합니다. 이러한 클러스터형 인덱스는 기본 테이블이 분할되는 경우 동일한 파티션 구성표와 파티션 함수를 사용해 기본 XML 인덱스가 분할되도록 합니다.

  • XML 인덱스가 있는 경우에는 테이블의 클러스터형 기본 키를 수정할 수 없습니다. 기본 키를 수정하기 전 테이블의 모든 XML 인덱스를 삭제해야 합니다.

  • 기본 XML 인덱스는 단일 xml 형식 열에서 만들 수 있습니다. xml 형식 열을 키 열로 하는 다른 인덱스 형식은 만들 수 없습니다. 그러나 xml 형식 열을 비 XML 인덱스에 포함할 수는 있습니다. 테이블의 각 xml 형식 열에는 자체의 고유한 기본 XML 인덱스가 있을 수 있습니다. 그러나 각 xml 형식 열에는 하나의 기본 XML 인덱스만 허용됩니다.

  • XML 인덱스는 비 XML 인덱스와 동일한 네임스페이스에 있습니다. 따라서 동일 테이블에서 XML 인덱스와 비 XML 인덱스의 이름이 같을 수는 없습니다.

  • XML 인덱스의 경우 IGNORE_DUP_KEY 및 ONLINE 옵션은 항상 OFF로 설정합니다. 이러한 옵션의 값을 OFF로 지정할 수 있습니다.

  • 사용자 테이블의 파일 그룹 또는 분할 정보는 XML 인덱스에 적용되며 별도로 지정할 수 없습니다.

  • DROP_EXISTING 인덱스 옵션은 기본 XML 인덱스를 삭제하고 새 기본 XML 인덱스를 만들거나, 보조 XML 인덱스를 삭제하고 새 보조 XML 인덱스를 만들 수 있습니다. 그러나 이 옵션에서는 보조 XML 인덱스를 삭제해 새로운 기본 XML 인덱스를 만들거나 그 반대를 실행할 수 없습니다.

  • 기본 XML 인덱스 이름은 보기 이름과 동일한 제한 사항이 있습니다.

    보기의 xml 형식 열이나 xml 형식 열을 포함한 테이블 반환 변수, 또는 xml 형식 변수에는 XML 인덱스를 만들 수 없습니다.

  • ALTER TABLE ALTER COLUMN 옵션을 사용해 형식화되지 않은 XML에서 형식화된 XML로, 또는 그 반대로 xml 형식 열을 변경하려면 열에는 어떠한 XML 인덱스도 있어서는 안 됩니다. XML 인덱스가 있다면 열 형식을 변경하기 전 이를 삭제해야 합니다.

  • XML 인덱스를 만들 때는 ARITHABORT 옵션을 ON으로 설정해야 합니다. xml 데이터 형식 메서드를 사용해 XML 열의 값을 쿼리, 삽입, 삭제, 또는 업데이트하려면 연결에서 동일한 옵션을 설정해야 합니다. 그렇지 않을 경우 xml 데이터 형식 메서드는 실패합니다.

    참고 항목

    XML 인덱스에 대한 정보는 카탈로그 뷰에서 찾을 수 있습니다. 그러나 sp_helpindex는 지원되지 않습니다. 이 항목의 후반부에 제공된 예에서는 카탈로그 뷰를 쿼리하여 XML 인덱스 정보를 찾는 방법을 보여 줍니다.

값이 1년 미만인 XML 스키마 형식xs:date 또는 xs:dateTime(또는 이러한 형식의 하위 형식)을 포함하는 xml 데이터 형식 열에 기본 XML 인덱스를 만들거나 다시 만드는 경우 SQL Server 2008(10.0.x) 이상 버전에서는 인덱스 생성이 실패합니다. SQL Server 2005(9.x)에서 이러한 값이 허용되었으므로 SQL Server 2005(9.x)에서 생성된 데이터베이스에서 인덱스를 만들 때는 이러한 문제가 발생할 수 있습니다. 자세한 내용은 형식화된 XML과 형식화되지 않은 XML 비교를 참조하세요.

예제: 기본 XML 인덱스 만들기

형식화되지 않은 XML 열이 있는 테이블 T (pk INT PRIMARY KEY, xCol XML)이 대부분의 예제에서 사용됩니다. 이 예제는 형식화된 XML로 간단히 확장할 수 있습니다. 설명을 쉽게 하기 위해 쿼리는 아래 예시에서 보는 바와 같이 XML 데이터 인스턴스를 대상으로 기술됩니다.

<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">
   <title>Writing Secure Code</title>
   <author>
      <first-name>Michael</first-name>
      <last-name>Howard</last-name>
   </author>
   <author>
      <first-name>David</first-name>
      <last-name>LeBlanc</last-name>
   </author>
   <price>39.99</price>
</book>

다음 문은 테이블T의 XML 열 xCol에 idx_xCol이라는 이름의 XML 인덱스를 만듭니다.

CREATE PRIMARY XML INDEX idx_xCol on T (xCol)

보조 XML 인덱스 만들기

CREATE INDEX(Transact-SQL)Transact-SQL DDL 문을 사용해 보조 XML 인덱스를 만들고 원하는 보조 XML 인덱스의 형식을 지정합니다.

보조 XML 인덱스를 만들 때는 다음에 유의해야 합니다.

  • IGNORE_DUP_KEY 및 ONLINE을 제외하고 비클러스터형 인덱스에 적용되는 모든 인덱싱 옵션을 보조 XML 인덱스에서 사용할 수 있습니다. 보조 XML 인덱스의 경우 두 옵션은 항상 OFF로 설정되어야 합니다.

  • 보조 인덱스는 기본 XML 인덱스와 똑같이 분할됩니다.

  • DROP_EXISTING은 사용자 테이블의 보조 인덱스를 삭제하고 다른 보조 인덱스를 사용자 테이블에 만들 수 있습니다.

sys.indexes 카탈로그 뷰를 쿼리해 XML 인덱스 정보를 검색할 수 있습니다. sys.xml_indexes 카탈로그 뷰의 secondary_type_desc 열은 보조 인덱스 형식을 제공합니다.

SELECT  *
FROM    sys.xml_indexes;

secondary_type_desc 열에 반환되는 값은 NULL, PATH, VALUE, 또는 PROPERTY일 수 있습니다. 기본 XML 인덱스의 경우 반환 값은 NULL입니다.

예제: 보조 XML 인덱스 만들기

다음 예에서는 보조 XML 인덱스가 만들어지는 방법을 설명합니다. 또한 만들어진 XML 인덱스에 대한 정보도 보여 줍니다.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML);
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol
ON T(XmlCol);
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_T_XmlCol_PATH ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PATH;
GO
CREATE XML INDEX PIdx_T_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR VALUE;
GO
CREATE XML INDEX PIdx_T_XmlCol_PROPERTY ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PROPERTY;
GO

sys.xml_indexes 카탈로그 뷰를 쿼리해 XML 인덱스 정보를 검색할 수 있습니다. secondary_type_desc 열은 보조 인덱스 형식을 제공합니다.

SELECT  *
FROM    sys.xml_indexes;

카탈로그 뷰에서 인덱스 정보를 쿼리할 수도 있습니다.

SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T');

샘플 데이터를 추가한 다음 XML 인덱스 정보를 검토할 수 있습니다.

INSERT INTO T VALUES (1,
'<doc id="123">
<sections>
<section num="2">
<heading>Background</heading>
</section>
<section num="3">
<heading>Sort</heading>
</section>
<section num="4">
<heading>Search</heading>
</section>
</sections>
</doc>');
GO
-- Check XML index information.
SELECT *
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED');
GO
-- Space usage of primary XML index
DECLARE @index_id int;
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i
WHERE   i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T';

SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED');
GO
--- Space usage of secondary XML index (for example PATH secondary index)  PIdx_T_XmlCol_PATH
DECLARE @index_id int;
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i
WHERE  i.name = 'PIdx_T_XmlCol_PATH' and object_name(i.object_id) = 'T';

SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED');
GO

-- Space usage of all secondary XML indexes for a particular table
SELECT i.name, object_name(i.object_id), stats.*
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats
JOIN sys.xml_indexes i ON (stats.object_id = i.object_id and stats.index_id = i.index_id)
WHERE secondary_type is not null;
-- Drop secondary indexes.
DROP INDEX PIdx_T_XmlCol_PATH ON T;
GO
DROP INDEX PIdx_T_XmlCol_VALUE ON T;
GO
DROP INDEX PIdx_T_XmlCol_PROPERTY ON T;
GO
-- Drop primary index.
DROP INDEX PIdx_T_XmlCol ON T;
-- Drop table T.
DROP TABLE T;
GO

참고 항목