Membuat indeks XML
Berlaku untuk:
SQL ServerAzure SQL Database Azure SQL Managed Instance
Artikel ini menjelaskan cara membuat indeks XML primer dan sekunder.
Membuat indeks XML utama
Untuk membuat indeks XML utama, gunakan pernyataan CREATE INDEX (Transact-SQL)Transact-SQL DDL. Tidak semua opsi yang tersedia untuk indeks non-XML didukung pada indeks XML.
Perhatikan hal berikut saat Anda membuat indeks XML:
Untuk membuat indeks XML utama, tabel yang berisi kolom XML yang sedang diindeks, yang disebut tabel dasar, harus memiliki indeks berkluster pada kunci utama. Indeks berkluster ini memastikan bahwa jika tabel dasar dipartisi, indeks XML utama dapat dipartisi dengan menggunakan skema partisi dan fungsi partisi yang sama.
Jika ada indeks XML, kunci utama tabel yang diklusterkan tidak dapat dimodifikasi. Anda harus menghilangkan semua indeks XML pada tabel sebelum memodifikasi kunci utama.
Indeks XML utama dapat dibuat pada satu kolom jenis xml . Anda tidak dapat membuat jenis indeks lain dengan kolom jenis xml sebagai kolom kunci. Namun, Anda dapat menyertakan kolom jenis xml dalam indeks non-XML. Setiap kolom jenis xml dalam tabel dapat memiliki indeks XML utamanya sendiri. Namun, hanya satu indeks XML utama per kolom jenis xml yang diizinkan.
Indeks XML ada di namespace yang sama dengan indeks non-XML. Oleh karena itu, Anda tidak dapat memiliki indeks XML dan indeks non-XML pada tabel yang sama dengan nama yang sama.
opsi IGNORE_DUP_KEY dan ONLINE selalu diatur ke NONAKTIF untuk indeks XML. Anda dapat menentukan opsi ini dengan nilai NONAKTIF.
Informasi grup file atau pemartisian tabel pengguna diterapkan ke indeks XML, dan tidak dapat ditentukan secara terpisah.
Opsi indeks DROP_EXISTING dapat menghilangkan indeks XML utama dan membuat indeks XML utama baru, atau menghilangkan indeks XML sekunder dan membuat indeks XML sekunder baru. Namun, opsi ini tidak dapat menghilangkan indeks XML sekunder untuk membuat indeks XML utama baru atau sebaliknya.
Nama indeks XML utama memiliki batasan yang sama dengan nama tampilan.
Anda tidak dapat membuat indeks XML pada kolom jenis xml dalam tampilan, pada variabel bernilai tabel dengan kolom jenis xml , atau variabel jenis xml .
Untuk mengubah kolom jenis xml dari xml yang tidak dititik menjadi XML yang ditik, atau sebaliknya, dengan menggunakan opsi UBAH KOLOM ALTER TABLE, tidak ada indeks XML pada kolom yang seharusnya ada. Jika ada, itu harus dihilangkan sebelum perubahan tipe kolom dicoba.
Opsi ARITHABORT harus diatur ke AKTIF saat indeks XML dibuat. Untuk mengkueri, menyisipkan, menghapus, atau memperbarui nilai di kolom XML menggunakan metode tipe data xml , opsi yang sama harus diatur pada koneksi. Jika tidak, metode jenis data xml akan gagal.
Catatan
Informasi tentang indeks XML dapat ditemukan dalam tampilan katalog. Namun, sp_helpindex tidak didukung. Contoh yang disediakan nanti dalam topik ini memperlihatkan cara mengkueri tampilan katalog untuk menemukan informasi indeks XML.
Saat membuat atau membuat ulang indeks XML utama pada kolom tipe data xml yang berisi nilai jenis xs:date Skema XML atau xs:dateTime (atau subjenis jenis ini) yang memiliki tahun kurang dari 1, pembuatan indeks akan gagal di SQL Server 2008 (10.0.x) dan versi yang lebih baru. SQL Server 2005 (9.x) mengizinkan nilai-nilai ini, sehingga masalah ini dapat terjadi saat membuat indeks dalam database yang dihasilkan di SQL Server 2005 (9.x). Untuk informasi selengkapnya, lihat Membandingkan XML Yang Ditik dengan XML Yang Tidak Ditiru.
Contoh: Membuat indeks XML utama
Tabel T (pk INT PRIMARY KEY, xCol XML) dengan kolom XML yang tidak dijenis digunakan di sebagian besar contoh. Contoh ini dapat diperluas untuk mengetik XML dengan cara yang mudah. Untuk kesederhanaan, kueri dijelaskan untuk instans data XML seperti yang ditunjukkan dalam sampel berikut:
<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>
Pernyataan berikut membuat indeks XML, yang disebut idx_xCol, pada kolom xCol XML tabel T:
CREATE PRIMARY XML INDEX idx_xCol on T (xCol)
Membuat indeks XML sekunder
Gunakan pernyataan CREATE INDEX (Transact-SQL)Transact-SQL DDL untuk membuat indeks XML sekunder dan tentukan jenis indeks XML sekunder yang Anda inginkan.
Perhatikan hal berikut saat Anda membuat indeks XML sekunder:
Semua opsi pengindeksan yang berlaku untuk indeks non-kluster, kecuali IGNORE_DUP_KEY dan ONLINE, diizinkan pada indeks XML sekunder. Dua opsi harus selalu diatur ke NONAKTIF untuk indeks XML sekunder.
Indeks sekunder dipartisi sama seperti indeks XML utama.
DROP_EXISTING dapat menghilangkan indeks sekunder pada tabel pengguna dan membuat indeks sekunder lain pada tabel pengguna.
Anda bisa mengkueri tampilan katalog sys.xml_indexes untuk mengambil informasi indeks XML. Kolom secondary_type_desc dalam tampilan katalog sys.xml_indexes menyediakan jenis indeks sekunder:
SELECT *
FROM sys.xml_indexes;
Nilai yang secondary_type_desc dikembalikan dalam kolom dapat berupa NULL, PATH, VALUE, atau PROPERTY. Untuk indeks XML utama, nilai yang dikembalikan adalah NULL.
Contoh: Membuat indeks XML sekunder
Contoh berikut mengilustrasikan bagaimana indeks XML sekunder dibuat. Contohnya juga memperlihatkan informasi tentang indeks XML yang Anda buat.
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
Anda bisa mengkueri sys.xml_indexes tampilan katalog untuk mengambil informasi indeks XML. Kolom secondary_type_desc menyediakan jenis indeks sekunder.
SELECT *
FROM sys.xml_indexes;
Anda juga dapat mengkueri tampilan katalog untuk informasi indeks.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T');
Anda dapat menambahkan data sampel lalu meninjau informasi indeks 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