共用方式為


建立 XML 索引

適用於:SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

本文說明如何建立主要和次要 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 類型資料行的 table 值變數或是在 xml 類型變數上建立 XML 索引。

  • 若要使用 ALTER TABLE ALTER COLUMN 選項,將 xml 類型資料行從不具類型變更為具類型的 XML (反之亦然),則在資料行上就不應存在任何 XML 索引。 如果 XML 索引確實存在,必須在嘗試變更資料行類型前先卸除它。

  • 在建立 XML 索引時,必須將 ARITHABORT 選項設定為 ON。 若要使用 xml 資料類型方法查詢、插入、刪除或更新 XML 資料行中的值,必須在連接上設定相同的選項。 若未設定,xml 資料類型方法將會失敗。

    注意

    在目錄檢視中可以找到關於 XML 索引的相關資訊。 不過,並不支援 sp_helpindex 。 在本主題後面所提供的範例將示範如何查詢目錄檢視,以尋找 XML 索引資訊。

在包含 XML 結構描述類型 xs:datexs:dateTime (或是這些類型的任何子類型) 值 (該值的年份小於 1) 的 xml 資料類型資料行上建立或重新建立主要 XML 索引時,SQL Server 2008 (10.0.x) 和更新版本中的索引建立會失敗。 SQL Server 2005 (9.x) 允許這些值,所以在 SQL Server 2005 (9.x) 中產生之資料庫內建立索引時,可能會發生這個問題。 如需詳細資訊,請參閱 比較具類型的 XML 與不具類型的 XML

範例:建立主要 XML 索引

在大部分的範例中,都是使用資料表 T (pk INT PRIMARY KEY, xCol XML) 和不具類型的 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

另請參閱