다음을 통해 공유

xml 데이터 형식 열의 인덱스

  • 아티클

XML 인스턴스는 BLOB(Binary Large Object)으로 xml 유형 열에 저장되어 있습니다. 이러한 XML 인스턴스는 크기가 클 수 있으며 xml 데이터 유형 인스턴스의 저장된 이진 표현은 최대 2GB까지 될 수 있습니다. 이러한 BLOB은 인덱스 없이 런타임 시 단편화되어 쿼리를 계산할 수 있습니다. 이 단편화 작업에는 시간이 많이 걸릴 수 있습니다. 예를 들어 다음 쿼리를 참조하십시오.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

WHERE 절의 조건에 맞는 XML 인스턴스를 선택할 수 있도록 Production.ProductModel 테이블의 각 행에서 XML BLOB이 런타임 시 단편화됩니다. 그런 다음 exist() 메서드의 (/PD:ProductDescription/@ProductModelID[.="19"]) 식이 계산됩니다. 이러한 런타임 단편화는 열에 저장된 인스턴스의 크기와 수에 따라 비용이 많이 들 수 있습니다.

XML BLOB을 쿼리하는 것이 사용자의 응용 프로그램 환경에서 일반적인 경우 이것은 xml 유형 열을 인덱싱하는 데 도움이 됩니다. 그러나 데이터 수정 중 인덱스를 유지 관리하는 비용이 듭니다.

XML 인덱스는 다음 범주로 구분됩니다.

  • 기본 XML 인덱스
  • 보조 XML 인덱스

xml 유형 열의 첫 번째 인덱스는 기본 XML 인덱스여야 합니다. 기본 XML 인덱스를 사용하면 PATH, VALUE 및 PROPERTY 유형의 보조 인덱스가 지원됩니다. 이러한 보조 인덱스는 쿼리 유형에 따라 쿼리 성능을 향상시킬 수 있습니다.

기본 XML 인덱스

기본 XML 인덱스는 xml 데이터 유형 열의 XML BLOB을 지속적인 단편 형태로 표현한 것입니다. 인덱스는 열의 XML BLOB마다 여러 개의 데이터 행을 만듭니다. 인덱스의 행 수는 대략 XML BLOB의 노드 수와 같습니다.

각 행에는 다음 노드 정보를 저장합니다.

  • 요소 또는 특성 이름과 같은 태그 이름
  • 노드 값
  • 요소 노드, 특성 노드 또는 텍스트 노드와 같은 노드 유형
  • 내부 노드 식별자에 의해 표현되는 문서 순서 정보
  • 각 노드에서 XML 트리의 루트에 대한 경로. 이 열은 쿼리의 경로 식으로 검색됩니다.
  • 기본 테이블의 기본 키. 기본 테이블의 기본 키는 기본 테이블과 백 조인을 위한 기본 XML 인덱스에 복제되고 해당 기본 테이블의 기본 키에 있는 최대 열 수는 15개로 제한됩니다.

이 노드 정보는 지정된 쿼리에 대해 XML 결과를 계산 및 구성하는 데 사용됩니다. 최적화를 위해 태그 이름과 노드 유형 정보는 정수 값으로 인코딩되고 경로 열에서는 같은 인코딩을 사용합니다. 또한 경로는 경로 접미사가 알려져 있는 경우에만 경로에 일치할 수 있도록 반대 순서로 저장됩니다. 예를 들면 다음과 같습니다.

  • //ContactRecord/PhoneNumber - 마지막 두 단계만 알려져 있는 경우

또는

  • /Book/*/Title - 와일드카드 문자(*)가 식 중간에 지정되어 있는 경우

쿼리 프로세서에서는 xml 데이터 유형 메서드를 포함하는 쿼리에 대해 기본 XML 인덱스를 사용하고 기본 인덱스 자체에서 스칼라 값이나 XML 하위 트리를 반환합니다. 이 인덱스는 XML 인스턴스를 재구성하는 데 필요한 모든 정보를 저장합니다.

예를 들어 다음 쿼리는 ProductModel 테이블의 CatalogDescriptionxml 유형 열에 저장된 요약 정보를 반환합니다. 이 쿼리는 카탈로그 설명에 <Features> 설명도 저장되어 있는 제품 모델에 대해서만 <Summary> 정보를 반환합니다.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

기본 XML 인덱스에 관해 기본 테이블에서 각 XML BLOB 인스턴스를 단편화하는 대신, 각 XML BLOB에 해당하는 인덱스의 행이 exist() 메서드에 지정된 식에 대해 순서대로 검색됩니다. 경로를 인덱스의 경로 열에서 찾은 경우 <Summary> 요소와 그 하위 트리는 기본 XML 인덱스에서 함께 검색되고 query() 메서드의 결과로 XML BLOB으로 변환됩니다.

전체 XML 인스턴스를 검색할 때는 기본 XML 인덱스가 사용되지 않습니다. 예를 들어 다음 쿼리는 테이블에서 특정 제품 모델에 대한 제조 지침을 나타내는 전체 XML 인스턴스를 검색합니다.

USE AdventureWorks;

SELECT Instructions
FROM Production.ProductModel 
WHERE ProductModelID=7;

보조 XML 인덱스

검색 성능을 향상시키기 위해 보조 XML 인덱스를 만들 수 있습니다. 기본 XML 인덱스가 있어야 보조 인덱스를 만들 수 있습니다. 유형은 다음과 같습니다.

  • PATH 보조 XML 인덱스
  • VALUE 보조 XML 인덱스
  • PROPERTY 보조 XML 인덱스

PATH 보조 XML 인덱스

쿼리에서 일반적으로 xml 유형 열에 경로 식을 지정하는 경우에는 PATH 보조 인덱스로 검색 속도를 높일 수도 있습니다. 이 항목의 앞에서 설명한 대로 기본 인덱스는 exist() 메서드를 WHERE 절에 지정하는 쿼리가 있는 경우에 유용합니다. 또한 PATH 보조 인덱스를 추가하면 이러한 쿼리에서 검색 성능을 향상시킬 수 있습니다.

기본 XML 인덱스에서 런타임 시 XML BLOB의 단편화는 피할 수 있지만 경로 식을 사용하는 쿼리의 최대 성능은 제공할 수 없습니다. XML BLOB에 해당하는 기본 XML 인덱스의 모든 행이 대규모의 XML 인스턴스에 대해 순서대로 검색되기 때문에 순차적 검색의 속도가 느려질 수 있습니다. 이 경우 기본 인덱스의 경로 값과 노드 값에 보조 인덱스를 만들면 인덱스 검색의 속도가 현저히 빨라질 수 있습니다. PATH 보조 인덱스에서 경로 값 및 노드 값은 경로 검색 시 효율적으로 검색할 수 있는 키 열입니다. 쿼리 최적화 프로그램에서는 다음과 같은 식에 대해 PATH 인덱스를 사용할 수 있습니다.

  • /root/Location - 경로만 지정한 경우

또는

  • /root/Location/@LocationID[.="10"] - 경로 값과 노드 값이 모두 지정되어 있는 경우

다음 쿼리에서는 PATH 인덱스가 유용하게 사용되는 경우를 보여 줍니다.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

쿼리에서 exist() 메서드의 경로 식 /PD:ProductDescription/@ProductModelID 및 "19" 값은 PATH 인덱스의 키 필드에 해당합니다. 따라서 PATH 인덱스에서 직접 찾을 수 있고 기본 인덱스의 경로 값에 대한 순차적 검색보다 더 나은 검색 성능을 제공합니다.

VALUE 보조 XML 인덱스

예를 들어 쿼리가 값을 기반으로 하는 /Root/ProductDescription/@*[. = "Mountain Bike"] 또는 //ProductDescription[@Name = "Mountain Bike"]이고 경로가 완전히 지정되지 않거나 와일드카드를 포함하는 경우에는 기본 XML 인덱스의 노드 값에 보조 XML 인덱스를 만들어 더 빠른 결과를 얻을 수 있습니다.

VALUE 인덱스의 키 열은 기본 XML 인덱스의 노드 값 및 경로입니다. 값을 포함하는 요소 또는 특성 이름을 알 필요 없이 XML 인스턴스에서 해당 값을 쿼리하는 작업이 포함되는 경우 VALUE 인덱스가 유용합니다. 예를 들어 다음 식에 VALUE 인덱스가 있는 것이 좋습니다.

  • //author[LastName="someName"] - <LastName> 요소의 값은 알지만 <author> 부모가 아무 곳에서나 발생할 수 있는 경우
  • /book[@* = "someValue"] - 쿼리가 "someValue" 값이 있는 일부 특성을 가진 <book> 요소를 찾는 경우

다음 쿼리에서는 Contact 테이블에서 ContactID를 반환합니다. WHERE 절은 AdditionalContactInfoxml 유형 열에서 값을 찾는 필터를 지정합니다. 연락처 ID는 해당되는 추가 연락처 정보 XML BLOB에 특정 전화 번호가 포함되는 경우에만 반환됩니다. <telephoneNumber> 요소가 XML의 아무 위치에서나 나타날 수 있기 때문에 경로 식은 하위 또는 자체 축을 지정합니다.

WITH XMLNAMESPACES (
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT)

SELECT ContactID 
FROM   Person.Contact
WHERE  AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1

이 경우 <number>에 대한 검색 값을 알지만 이 값은 <telephoneNumber> 요소의 자식으로 XML 인스턴스의 아무 위치에서나 나타날 수 있습니다. 이러한 유형의 쿼리를 사용하면 특정 값에 기반한 인덱스 조회의 장점을 활용할 수 있습니다.

PROPERTY 보조 인덱스

개별 XML 인스턴스에서 하나 이상의 값을 검색하는 쿼리는 PROPERTY 인덱스의 장점을 활용할 수 있습니다. 이 시나리오는 사용자가 xml 유형의 value() 메서드를 사용하여 개체 속성을 검색할 때 및 개체의 기본 키 값이 알려져 있을 때 발생합니다.

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

예를 들어 제품 모델 19의 경우 다음 쿼리는 value() 메서드를 사용하여 ProductModelID 및 ProductModelName 특성 값을 검색합니다. 기본 XML 인덱스 또는 기타 보조 XML 인덱스를 사용하는 대신 PROPERTY 인덱스를 사용하면 더 빨리 실행될 수 있습니다.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') as ModelID,
       CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') as ModelName        
FROM Production.ProductModel   
WHERE ProductModelID = 19

이 항목의 후반부에서 설명하는 차이점을 제외하면 xml 유형 열에 XML 인덱스를 만드는 것은 비-xml 유형 열에 인덱스를 만드는 것과 비슷합니다. 다음 Transact-SQL DDL 문은 XML 인덱스의 작성 및 관리에 사용될 수 있습니다.

기본 XML 인덱스 만들기

기본 XML 인덱스를 만들려면 CREATE PRIMARY XML INDEX 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 인덱스에 적용됩니다. 사용자는 XML 인덱스에서 이를 별도로 지정할 수 없습니다.
  • DROP_EXISTING 인덱스 옵션은 기본 XML 인덱스를 삭제하고 새 기본 XML 인덱스를 만들거나, 보조 XML 인덱스를 삭제하고 새 보조 XML 인덱스를 만들 수 있습니다. 그러나 이 옵션을 사용하여 보조 XML 인덱스를 삭제하고 새 기본 XML 인덱스를 만들거나 기본 XML 인덱스를 삭제하고 새 보조 XML 인덱스를 만들 수는 없습니다.
  • 기본 XML 인덱스 이름에는 뷰 이름과 같은 제한이 있습니다.

뷰의 xml 유형 열이나 xml 유형 열 또는 xml 유형 변수가 있는 table 값 변수에 XML 인덱스를 만들 수 없습니다.

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

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

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

보조 XML 인덱스 만들기

CREATE XML INDEX 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 인덱스 수정

ALTER INDEX Transact-SQL DDL 문을 사용하여 기존 XML 및 비-XML 인덱스를 수정할 수 있습니다. 그러나 모든 ALTER INDEX 옵션을 XML 인덱스에 사용할 수 있는 것은 아닙니다. XML 인덱스를 수정할 때 다음 옵션은 유효하지 않습니다.

  • 다시 작성 및 설정 옵션인 IGNORE_DUP_KEY는 XML 인덱스에 대해 유효하지 않습니다. 다시 작성 옵션 ONLINE은 보조 XML 인덱스에 대해 OFF로 설정해야 합니다. DROP_EXISTING 옵션은 ALTER INDEX 문에서 허용되지 않습니다. 인덱스를 다시 작성할 때 연결 옵션은 설정 옵션(XML 인덱스)에서 설명한 대로 설정해야 합니다.
  • 사용자 테이블에서 PRIMARY KEY 제약 조건을 수정하면 자동으로 XML 인덱스에 전파되지 않습니다. 사용자는 먼저 XML 인덱스를 삭제한 다음 다시 작성해야 합니다.
  • ALTER INDEX ALL이 지정되면 비-XML 인덱스 및 XML 인덱스 모두에 적용됩니다. 인덱싱 옵션은 두 가지 인덱스 유형에 모두 유효하지 않도록 지정할 수 있습니다. 이 경우 전체 문이 실패합니다.

XML 인덱스 삭제

DROP INDEX Transact-SQL 문을 사용하여 기존 기본 또는 보조 XML 인덱스 및 비-XML 인덱스를 삭제할 수 있습니다. 그러나 DROP INDEX의 옵션은 XML 인덱스에 적용되지 않습니다. 기본 XML 인덱스를 삭제하려면 존재하는 보조 인덱스도 모두 삭제됩니다.

TableName**.**IndexName이 있는 DROP 구문은 단계적으로 제거하며 XML 인덱스에 대해서는 지원되지 않습니다.

예

다음 예에서는 생성, 수정 및 삭제 중인 XML 인덱스를 보여 줍니다.

1. 기본 XML 인덱스 만들기 및 삭제

다음 예에서는 XML 인덱스가 xml 유형 열에 생성됩니다.

DROP TABLE T
GO
CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create Primary XML index 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Verify the index creation. 
-- Note index type is 3 for xml indexes.
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol' 
-- Drop the index.
DROP INDEX PIdx_T_XmlCol ON T

테이블이 삭제될 때 그 테이블의 모든 XML 인덱스도 자동으로 삭제됩니다. 그러나 XML 인덱스가 열에 있는 경우 XML 열은 테이블에서 삭제할 수 없습니다.

다음 예에서는 XML 인덱스가 xml 유형 열에 생성됩니다. 자세한 내용은 형식화된 XML과 형식화되지 않은 XML을 참조하십시오.

CREATE TABLE TestTable(
 Col1 int primary key, 
 Col2 xml (Production.ProductDescriptionSchemaCollection)) 
GO

이제 Co12에 기본 XML 인덱스를 만들 수 있습니다.

CREATE PRIMARY XML INDEX PIdx_TestTable_Col2 
ON TestTable(Col2)
GO

2. 보조 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

3. XML 인덱스 수정

다음 예에서는 XML 인덱스가 생성되고 ALLOW_ROW_LOCKS 옵션을 OFF로 설정하여 수정됩니다. ALLOW_ROW_LOCKS가 OFF일 때는 행이 잠기지 않고 페이지 수준 및 테이블 수준의 잠금을 사용하여 지정된 인덱스에 대한 액세스가 설정됩니다.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary XML index. 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'

-- Modify and set an index option.
ALTER INDEX PIdx_T_XmlCol on T 
SET (ALLOW_ROW_LOCKS = OFF)

4. XML 인덱스 활성화 및 비활성화

기본적으로 XML 인덱스는 활성화됩니다. XML 인덱스가 비활성화되어 있는 경우 XML 열에 대해 실행되는 쿼리에서는 해당 XML 인덱스를 사용할 수 없습니다. XML 인덱스를 활성화하려면 REBUILD 옵션과 함께 ALTER INDEX를 사용합니다.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON T(XmlCol)
GO
ALTER INDEX PIdx_T_XmlCol on T DISABLE
Go
-- Verify index is disabled.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'
-- Rebuild the index.
ALTER INDEX PIdx_T_XmlCol on T REBUILD
Go

5. DROP_EXISTING 인덱스 옵션을 사용하여 XML 인덱스 만들기

다음 예에서는 XML 인덱스가 XmlColx 열에 생성됩니다. 그런 다음 같은 이름으로 된 다른 XML 인덱스가 다른 열 XmlColy에 생성됩니다. DROP_EXISTING 옵션이 지정되어 있으므로 (XmlColx)의 기존 XML 인덱스가 삭제되고 (XmlColy)의 새 XML 인덱스가 생성됩니다.

DROP TABLE T
GO
CREATE TABLE T(Col1 int primary key, XmlColx xml, XmlColy xml)
GO
-- Create XML index on XmlColx.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColx)
GO
-- Create same name XML index on XmlColy.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColy) 
WITH (DROP_EXISTING = ON)
-- Verify the index is created on XmlColy.d.
SELECT sc.name 
FROM   sys.xml_indexes si inner join sys.index_columns sic 
ON     sic.object_id=si.object_id and sic.index_id=si.index_id
INNER  join sys.columns sc on sc.object_id=sic.object_id 
AND    sc.column_id=sic.column_id
WHERE  si.name='PIdx_T_XmlCol' 
AND    si.object_id=object_id('T')

이 쿼리에서는 지정된 XML 인덱스가 생성된 열 이름을 반환합니다.

참고 항목

개념

xml 데이터 형식
예제 XML 응용 프로그램

관련 자료

sys.dm_db_index_physical_stats

도움말 및 정보

SQL Server 2005 지원 받기