XML 인덱스 만들기

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure 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 인덱스가 있을 수 없습니다.

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

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

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

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

    xml 형식 열 또는 xml 형식 변수가 있는 테이블 반환 변수에는 뷰의 xml 형식 열에 XML 인덱스를 만들 수 없습니다.

  • XML 형식 열을 형식화되지 않은 XML에서 형식화된 XML로 변경하거나 그 반대로 변경하려면 ALTER TABLE ALTER COLUMN 옵션을 사용하여 열에 XML 인덱스가 없어야 합니다. 열 형식 변경이 시도되기 전에 삭제해야 합니다.

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

    참고 항목

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

XML 스키마 형식의 값 또는 1년 미만의 하위 형식이 포함된 xml 데이터 형식 xs:date 열에서 기본 XML 인덱스를 만들거나 xs:dateTime 다시 만들면 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.xml_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

참고 항목