創建主XML索引註意事項若要創建主 XML 索引，請使用 CREATE INDEX (Transact-SQL) Transact-SQL DDL 語句。 XML 索引不完全支持可用於非 XML 索引的所有選項。創建 XML 索引時註意下列事項：若要創建主 XML 索引，含有被索引的 XML ...

創建主XML索引註意事項

若要創建主 XML 索引，請使用 CREATE INDEX (Transact-SQL) Transact-SQL DDL 語句。 XML 索引不完全支持可用於非 XML 索引的所有選項。

創建 XML 索引時註意下列事項：

若要創建主 XML 索引，含有被索引的 XML 列的表（稱為基表）必須具有主鍵的聚集索引。這確保了在對基表進行了分區的情況下，可以使用相同的分區方案和分區函數對主 XML 索引進行分區。
如果存在 XML 索引，則不能修改基表的聚集主鍵。在修改主鍵之前，必須刪除表的所有 XML 索引。
您可以對單個 xml 類型列創建主 XML 索引。您無法將 XML 類型列作為鍵列來創建任何其他類型的索引。但是，可以在非 XML 索引中包含 xml L 類型列。表中的每個 xml 類型列都可以有自己的主 XML 索引。但是，一個 xml 類型列只允許有一個主 XML 索引。
XML 索引和非 XML 索引存在於相同的命名空間中。因此，同一表的 XML 索引和非 XML 索引不能具有相同的名稱。
對於 XML 索引，IGNORE_DUP_KEY 選項和 ONLINE 選項始終設置為 OFF。您可以將這些選項的值指定為 OFF。
將用戶表的文件組和分區信息應用於 XML 索引。用戶無法單獨為 XML 索引指定這些信息。
DROP_EXISTING 索引選項可以刪除主 XML 索引並創建一個新的主 XML 索引，或者刪除輔助 XML 索引並創建一個新的輔助 XML 索引。但是，此選項不能通過刪除輔助 XML 索引來創建新的主 XML 索引，反之亦然。
主 XML 索引名稱與視圖名稱有相同的限制。

不能對視圖中的 xml 類型列、 xml 類型列的表值變數或 xml 類型變數創建 XML 索引。
若要使用 ALTER TABLE ALTER COLUMN 選項將 xml 類型列從非類型化的 XML 更改為類型化的 XML，或者從類型化的 XML 更改為非類型化的 XML，則列不應存在 XML 索引。如果確實存在，則在嘗試更改列類型之前必須刪除該索引。
創建 XML 索引時必須將選項 ARITHABORT 設置為 ON。若要使用 XML 數據類型方法查詢、刪除、更新 XML 列中的值或向 XML 列中插入值，則必須對連接設置相同的選項。如果沒有設置，則 XML 數據類型方法將會失敗。

備註

有關 XML 索引的信息可以在目錄視圖中找到。但是，不支持 sp_helpindex 。本主題後面部分提供的示例說明瞭如何查詢目錄視圖以查找 XML 索引信息。

如果 XML 數據類型列包含類型為 XML 架構類型 xs:date 或 xs:dateTime （或這些類型的任何子類型）的值且這些值中的年份小於 1，則在對這樣的 XML 數據類型列創建或重新創建主 XML 索引時，索引創建操作在 SQL Server 2008 和更高版本中將失敗。 SQL Server 2005 允許使用這些值，因此在 SQL Server 2005中生成的資料庫中創建索引時可能會出現這種問題。有關詳細信息，請參閱類型化的 XML 與非類型化的 XML 的比較。

使用SSMS資料庫管理工具創建主XML索引

使用表設計器創建主XML索引

1、連接資料庫，選擇資料庫，選擇數據表-》右鍵點擊-》選擇設計。

2、在表設計器視窗-》選擇要創建xml索引的數據列-》右鍵點擊-》選的xml索引。

3、在xml索引彈出框-》點擊添加，添加索引-》輸入索引名稱-》輸入索引描述-》表設計器可以選擇預設-》點擊關閉。

4、點擊保存(或者ctrl+s)-》關閉表設計器-》刷新表-》查詢結果。

使用對象資源管理器創建主XML索引

1、連接資料庫，選擇資料庫，選擇數據表-》展開數據表-》右鍵點擊索引-》選擇新建索引-》選擇主XML索引。

2、在新建索引彈出框-》輸入索引名稱-》點擊添加選擇索引數據列。

3、在數據列彈出框-》選擇數據列-》點擊確定。

4、在新建索引彈出框-》點擊選項-》主XML索引屬性可以自己設置，也可以選擇系統預設。

5、在新建索引彈出框-》選擇擴展屬性-》輸入擴展屬性名稱和值-》輸入完成點擊確定。

6、不需要刷新表可直接查看結果。

使用T-SQL腳本創建主XML索引

語法：

--聲明資料庫引用
use 資料庫;
go

--判斷xml索引是否存在，如果存在則先刪除在創建
if exists(select * from sys.indexes where name=索引名稱)
drop index 索引名稱 on 表名;
go

--添加xml數據類型數據列
create
primary --聲明為主索引
xml --聲明為xml索引
index --聲明創建索引
索引名稱--聲明索引名稱
on 表名--聲明索引所在表
(列名) --聲明索引在哪個數據列
with(
--pad_index:指定索引填充
--pad_index=on:FILLFACTOR 指定的可用空間百分比應用於索引的中間級頁。
--pad_index=off或未指定 fillfactor:考慮到中間級頁上的鍵集，可以將中間級頁幾乎填滿，但至少要為最大索引行留出足夠空間。
pad_index={ on | off },

--fillfactor=n：指定一個百分比，指示在資料庫引擎創建或修改索引的過程中，應將每個索引頁面的葉級填充到什麼程度。指定的值必須是 1 到 100 之間的整數。預設值為 0。
fillfactor=n,

--statistics_norecompute:指定是否重新計算統計信息。
--statistics_norecompute=on:過時的統計信息不會自動重新計算。
--statistics_norecompute=off:啟用自動統計信息更新。
statistics_norecompute={ on | off },

--aloow_row_locks:指定是否允許行鎖。
--allow_row_locks=on:訪問索引時允許行鎖。資料庫引擎確定何時使用行鎖。
--allow_row_locks=off:不使用行鎖。
allow_row_locks={ on | off },

--allow_page_locks:指定是否允許使用頁鎖。
--allow_page_locks=on:訪問索引時允許頁鎖。資料庫引擎確定何時使用頁鎖。
--allow_page_locks=off:不使用頁鎖。
allow_page_locks={ on | off },

--drop_existing：表示如果這個索引還在表上就 drop 掉然後在 create 一個新的。預設為 OFF。
--drop_existing=on:指定要刪除並重新生成現有索引，其必須具有相同名稱作為參數 index_name。
--drop_existing=off:指定不刪除和重新生成現有的索引。如果指定的索引名稱已經存在，SQL Server 將顯示一個錯誤。
drop_existing={ on | off },

--sort_in_tempdb:指定是否將排序結果存儲在 tempdb 中。
--sort_in_tempdb=on:在tempdb中存儲用於生成索引的中間排序結果。如果tempdb與用戶資料庫不在同一組磁碟上，就可縮短創建索引所需的時間。但是，這會增加索引生成期間所使用的磁碟空間量。
--sort_in_tempdb=off:中間排序結果與索引存儲在同一資料庫中。
sort_in_tempdb={ on | off },

--online:指定在索引操作期間基礎表和關聯的索引是否可用於查詢和數據修改操作。預設為 OFF。 REBUILD 可作為 ONLINE 操作執行。
--online=on:在索引操作期間不持有長期表鎖。在索引操作的主要階段，源表上只使用意向共用 (IS) 鎖。
--這使得能夠繼續對基礎表和索引進行查詢或更新。
--操作開始時，在很短的時間內對源對象持有共用 (S) 鎖。
--操作結束時，如果創建非聚集索引，將在短期內獲取對源的 S（共用）鎖；
--當聯機創建或刪除聚集索引時，以及重新生成聚集或非聚集索引時，將在短期內獲取 SCH-M（架構修改）鎖。但聯機索引鎖是短的元數據鎖，特別是 Sch-M 鎖必須等待此表上的所有阻塞事務完成。
--在等待期間，Sch-M 鎖在訪問同一表時阻止在此鎖後等待的所有其他事務。對本地臨時表創建索引時，ONLINE 不能設置為 ON。
--online=off:在索引操作期間應用表鎖。這樣可以防止所有用戶在操作期間訪問基礎表。
--創建、重新生成或刪除聚集索引或者重新生成或刪除非聚集索引的離線索引操作將對錶獲取架構修改 (Sch-M) 鎖。
--這樣可以防止所有用戶在操作期間訪問基礎表。創建非聚集索引的離線索引操作將對錶獲取共用 (S) 鎖。這樣可以防止更新基礎表，但允許讀操作（如 SELECT 語句）。
online={ on | off },

--maxdop=max_degree_of_parallelism:在索引操作期間替代 max degree of parallelism 配置選項。有關詳細信息，請參閱配置 max degree of parallelism 伺服器配置選項。使用 MAXDOP 可以限制在執行並行計劃的過程中使用的處理器數量。最大數量為 64 個處理器。
--max_degree_of_parallelism 可以是：
--1 - 取消生成並行計劃。
-->1 - 將並行索引操作中使用的最大處理器數量限製為指定數量。
--0（預設值）- 根據當前系統工作負荷使用實際數量的處理器或更少數量的處理器。
--有關詳細信息，請參閱配置並行索引操作。
maxdop=max_degree_of_parallelism
)
go

--添加索引註釋
execute sp_addextendedproperty N'MS_Description',N'縮影描述',N'user',N'dbo',N'table',N'test1',N'index',N'說明名稱';
go

示例：

--聲明資料庫引用
use testss;
go

--判斷xml索引是否存在，如果存在則先刪除在創建
if exists(select * from sys.indexes where name='xmlindex')
drop index xmlindex on test1;
go

--添加xml數據類型數據列
create
primary --聲明為主索引
xml --聲明為xml索引
index --聲明創建索引
xmlindex --聲明索引名稱
on test1 --聲明索引所在表
(xml1) --聲明索引在哪個數據列
with(
--pad_index:指定索引填充
--pad_index=on:FILLFACTOR 指定的可用空間百分比應用於索引的中間級頁。
--pad_index=off或未指定 fillfactor:考慮到中間級頁上的鍵集，可以將中間級頁幾乎填滿，但至少要為最大索引行留出足夠空間。
pad_index=on,

--fillfactor=n：指定一個百分比，指示在資料庫引擎創建或修改索引的過程中，應將每個索引頁面的葉級填充到什麼程度。指定的值必須是 1 到 100 之間的整數。預設值為 0。
fillfactor=1,

--statistics_norecompute:指定是否重新計算統計信息。
--statistics_norecompute=on:過時的統計信息不會自動重新計算。
--statistics_norecompute=off:啟用自動統計信息更新。
statistics_norecompute=off,

--aloow_row_locks:指定是否允許行鎖。
--allow_row_locks=on:訪問索引時允許行鎖。資料庫引擎確定何時使用行鎖。
--allow_row_locks=off:不使用行鎖。
allow_row_locks=on,

--aloow_row_locks:指定是否允許行鎖。
--allow_row_locks=on:訪問索引時允許行鎖。資料庫引擎確定何時使用行鎖。
--allow_row_locks=off:不使用行鎖。
allow_page_locks=on,

--添加索引註釋
execute sp_addextendedproperty N'MS_Description',N'第一個xml數據列',N'user',N'dbo',N'table',N'test1',N'index',N'xmlindex';
go

創建主XML索引優缺點

優點：

1、可以完整、一致的表示XML的值。

2、XML 值相對較大，而檢索的部分相對較小。生成索引避免了在運行時分析所有數據，並能實現高效的查詢處理，從而使索引查找受益。

3、查詢效率會大大提高。

缺點：

1、增加存儲空間。

SQLServer之創建主XML索引