BUAT INDEKS XML (Transact-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Membuat indeks XML pada tabel tertentu. Indeks dapat dibuat sebelum ada data dalam tabel. Indeks XML dapat dibuat pada tabel di database lain dengan menentukan nama database yang memenuhi syarat.

Catatan

Untuk membuat indeks relasional, lihat CREATE INDEX (Transact-SQL). Untuk informasi tentang cara membuat indeks spasial, lihat MEMBUAT INDEKS SPASIAL (Transact-SQL).

Konvensi sintaks transact-SQL

Sintaksis

--Create XML Index   
CREATE [ PRIMARY ] XML INDEX index_name
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{ database_name.schema_name.table_name | schema_name.table_name | table_name }

<xml_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = OFF
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = OFF
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | XML_COMPRESSION = { ON | OFF }
}

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

[PRIMER] XML

Membuat indeks XML pada kolom xml yang ditentukan. Ketika PRIMARY ditentukan, indeks berkluster dibuat dengan kunci berkluster yang terbentuk dari kunci pengklusteran tabel pengguna dan pengidentifikasi simpul XML. Setiap tabel dapat memiliki hingga 249 indeks XML. Perhatikan hal berikut saat Anda membuat indeks XML:

  • Indeks berkluster harus ada pada kunci utama tabel pengguna.

  • Kunci pengklusteran tabel pengguna dibatasi hingga 15 kolom.

  • Setiap kolom xml dalam tabel dapat memiliki satu indeks XML utama dan beberapa indeks XML sekunder.

  • Indeks XML utama pada kolom xml harus ada sebelum indeks XML sekunder dapat dibuat pada kolom .

  • Indeks XML hanya dapat dibuat pada satu kolom xml . Anda tidak dapat membuat indeks XML pada kolom non-xml, anda juga tidak dapat membuat indeks relasional pada kolom xml.

  • Anda tidak dapat membuat indeks XML, baik primer atau sekunder, pada kolom xml dalam tampilan, pada variabel bernilai tabel dengan kolom xml , atau variabel jenis xml .

  • Anda tidak dapat membuat indeks XML utama pada kolom xml komputasi.

  • Pengaturan opsi SET harus sama dengan yang diperlukan untuk tampilan terindeks dan indeks kolom komputasi. Secara khusus, opsi ARITHABORT harus diatur ke AKTIF saat indeks XML dibuat dan saat menyisipkan, menghapus, atau memperbarui nilai di kolom xml .

Untuk informasi selengkapnya, lihat Indeks XML (SQL Server).

index_name

Nama indeks. Nama indeks harus unik dalam tabel tetapi tidak harus unik dalam database. Nama indeks harus mengikuti aturan pengidentifikasi.

Nama indeks XML utama tidak dapat dimulai dengan karakter berikut: #, , ##@, atau @@.

xml_column_name

Kolom xml tempat indeks berada. Hanya satu kolom xml yang dapat ditentukan dalam satu definisi indeks XML; namun, beberapa indeks XML sekunder dapat dibuat pada kolom xml .

MENGGUNAKAN xml_index_name INDEKS XML

Menentukan indeks XML utama yang akan digunakan dalam membuat indeks XML sekunder.

UNTUK { VALUE | JALUR | PROPERTI }

Menentukan jenis indeks XML sekunder.

VALUE
Membuat indeks XML sekunder pada kolom tempat kolom kunci berada (nilai dan jalur simpul) dari indeks XML utama.

PATH
Membuat indeks XML sekunder pada kolom yang dibangun berdasarkan nilai jalur dan nilai simpul dalam indeks XML utama. Dalam indeks sekunder PATH, jalur dan nilai simpul adalah kolom kunci yang memungkinkan pencarian yang efisien saat mencari jalur.

PROPERTI
Membuat indeks XML sekunder pada kolom (PK, jalur, dan nilai simpul) dari indeks XML utama di mana PK adalah kunci utama tabel dasar.

<object>::=

Objek yang sepenuhnya memenuhi syarat atau tidak memenuhi syarat untuk diindeks.

database_name
Nama database.

schema_name
Nama skema tempat tabel berada.

table_name
Nama tabel yang akan diindeks.

<> xml_index_option ::=

Menentukan opsi yang akan digunakan saat Anda membuat indeks.

PAD_INDEX = { ON | NONAKTIF }

Menentukan padding indeks. Defaultnya adalah NONAKTIF.

AKTIF
Persentase ruang kosong yang ditentukan oleh fillfactor diterapkan ke halaman tingkat menengah indeks.

OFF atau fillfactor tidak ditentukan
Halaman tingkat menengah diisi ke kapasitas dekat, menyisakan ruang yang cukup untuk setidaknya satu baris dari ukuran maksimum yang dapat dimiliki indeks, mengingat set kunci pada halaman perantara.

Opsi PAD_INDEX hanya berguna ketika FILLFACTOR ditentukan, karena PAD_INDEX menggunakan persentase yang ditentukan oleh FILLFACTOR. Jika persentase yang ditentukan untuk FILLFACTOR tidak cukup besar untuk memungkinkan satu baris, Mesin Database secara internal mengambil alih persentase untuk memungkinkan minimum. Jumlah baris pada halaman indeks menengah tidak pernah kurang dari dua, terlepas dari seberapa rendah nilai fillfactor.

FILLFACTOR = fillfactor

Menentukan persentase yang menunjukkan seberapa lengkap Mesin Database harus membuat tingkat daun setiap halaman indeks selama pembuatan atau pembangunan ulang indeks. fillfactor harus berupa nilai bilangan bulat dari 1 hingga 100. Defaultnya adalah 0. Jika fillfactor adalah 100 atau 0, Mesin Database membuat indeks dengan halaman daun yang diisi hingga kapasitas.

Catatan

Nilai faktor pengisian 0 dan 100 sama dalam semua hal.

Pengaturan FILLFACTOR hanya berlaku saat indeks dibuat atau dibangun kembali. Mesin Database tidak secara dinamis menyimpan persentase ruang kosong yang ditentukan di halaman. Untuk melihat pengaturan faktor pengisian, gunakan tampilan katalog sys.indexes .

Penting

Membuat indeks berkluster dengan FILLFACTOR kurang dari 100 memengaruhi jumlah ruang penyimpanan yang ditempati data karena Mesin Database mendistribusikan ulang data saat membuat indeks berkluster.

Untuk informasi selengkapnya, lihat Menentukan Faktor Pengisian untuk Indeks.

SORT_IN_TEMPDB = { AKTIF | NONAKTIF }

Menentukan apakah akan menyimpan hasil pengurutan sementara dalam tempdb. Defaultnya adalah NONAKTIF.

AKTIF
Hasil pengurutan menengah yang digunakan untuk membangun indeks disimpan dalam tempdb. Ini dapat mengurangi waktu yang diperlukan untuk membuat indeks jika tempdb berada di kumpulan disk yang berbeda dari database pengguna. Namun, ini meningkatkan jumlah ruang disk yang digunakan selama build indeks.

TIDAK AKTIF
Hasil pengurutan menengah disimpan dalam database yang sama dengan indeks.

Selain ruang yang diperlukan dalam database pengguna untuk membuat indeks, tempdb harus memiliki jumlah ruang tambahan yang sama untuk menyimpan hasil pengurutan menengah. Untuk informasi selengkapnya, lihat Opsi SORT_IN_TEMPDB Untuk Indeks.

IGNORE_DUP_KEY = NONAKTIF

Tidak berpengaruh untuk indeks XML karena jenis indeks tidak pernah unik. Jangan atur opsi ini ke AKTIF, atau kesalahan muncul.

DROP_EXISTING = { AKTIF | NONAKTIF }

Menentukan bahwa indeks XML bernama yang sudah ada sebelumnya dihilangkan dan dibangun kembali. Defaultnya adalah NONAKTIF.

AKTIF
Indeks yang ada dihilangkan dan dibangun kembali. Nama indeks yang ditentukan harus sama dengan indeks yang ada saat ini; namun, definisi indeks dapat dimodifikasi. Misalnya, Anda dapat menentukan kolom, urutan pengurutan, skema partisi, atau opsi indeks yang berbeda.

TIDAK AKTIF
Kesalahan ditampilkan jika nama indeks yang ditentukan sudah ada.

Jenis indeks tidak dapat diubah dengan menggunakan DROP_EXISTING. Selain itu, indeks XML utama tidak dapat didefinisikan ulang sebagai indeks XML sekunder, atau sebaliknya.

ONLINE = NONAKTIF

Menentukan bahwa tabel yang mendasari dan indeks terkait tidak tersedia untuk kueri dan modifikasi data selama operasi indeks. Dalam versi SQL Server ini, build indeks online tidak didukung untuk indeks XML. Jika opsi ini diatur ke AKTIF untuk indeks XML, kesalahan akan muncul. Hilangkan opsi ONLINE atau atur ONLINE ke NONAKTIF.

Operasi indeks offline yang membuat, membangun kembali, atau menghilangkan indeks XML, memperoleh kunci Modifikasi Skema (Sch-M) pada tabel. Ini mencegah semua akses pengguna ke tabel yang mendasar selama operasi.

Catatan

Operasi indeks online tidak tersedia di setiap edisi Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.

ALLOW_ROW_LOCKS = { AKTIF | NONAKTIF }

Menentukan apakah kunci baris diizinkan. Defaultnya adalah ON.

AKTIF
Kunci baris diperbolehkan saat mengakses indeks. Mesin Database menentukan kapan kunci baris digunakan.

TIDAK AKTIF
Kunci baris tidak digunakan.

ALLOW_PAGE_LOCKS = { AKTIF | NONAKTIF }

Menentukan apakah kunci halaman diizinkan. Defaultnya adalah ON.

AKTIF
Kunci halaman diizinkan saat mengakses indeks. Mesin Database menentukan kapan kunci halaman digunakan.

TIDAK AKTIF
Kunci halaman tidak digunakan.

MAXDOP = max_degree_of_parallelism

Mengambil alih opsi konfigurasi Konfigurasi Konfigurasi Server paralelisme maksimum selama operasi indeks. Gunakan MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

Penting

Meskipun opsi MAXDOP didukung secara sintis untuk semua indeks XML, untuk indeks XML utama, CREATE XML INDEX hanya menggunakan satu prosesor.

max_degree_of_parallelism dapat berupa:

1
Menekan pembuatan rencana paralel.

>1
Membatasi jumlah maksimum prosesor yang digunakan dalam operasi indeks paralel ke jumlah yang ditentukan atau lebih sedikit berdasarkan beban kerja sistem saat ini.

0 (default)
Menggunakan jumlah prosesor aktual atau lebih sedikit berdasarkan beban kerja sistem saat ini.

Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

Catatan

Operasi indeks paralel tidak tersedia di setiap edisi Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.

Keterangan

Kolom komputasi yang berasal dari jenis data xml dapat diindeks baik sebagai kunci atau kolom non-kunci yang disertakan selama jenis data kolom yang dikomputasi diizinkan sebagai kolom kunci indeks atau kolom nonkunci. Anda tidak dapat membuat indeks XML utama pada kolom xml komputasi.

Untuk melihat informasi tentang indeks XML, gunakan tampilan katalog sys.xml_indexes .

Untuk informasi selengkapnya tentang indeks XML, lihat Indeks XML (SQL Server).

Pemadatan XML

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

  • Indeks XML tidak mewarisi properti pemadatan tabel. Untuk memadatkan indeks, Anda harus secara eksplisit mengaktifkan kompresi XML pada indeks XML.
  • Indeks XML sekunder tidak mewarisi properti pemadatan indeks XML Utama.
  • Secara default, pengaturan pemadatan XML untuk indeks XML diatur ke NONAKTIF saat indeks dibuat.

Keterangan tambahan tentang pembuatan indeks

Untuk informasi selengkapnya tentang pembuatan indeks, lihat bagian "Keterangan" di CREATE INDEX (Transact-SQL).

Contoh

Artikel ini memerlukan AdventureWorks2022 database sampel, yang dapat Anda unduh dari beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

J. Membuat indeks XML utama

Contoh berikut membuat indeks XML utama pada CatalogDescription kolom dalam Production.ProductModel tabel.

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);  
GO

B. Membuat indeks XML utama dengan pemadatan XML

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

Contoh berikut membuat indeks XML utama pada CatalogDescription kolom dalam Production.ProductModel tabel.

IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription
        ON Production.ProductModel;  
GO  
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription)
    WITH (XML_COMPRESSION = ON);  
GO

C. Membuat indeks XML sekunder

Contoh berikut membuat indeks XML sekunder pada CatalogDescription kolom dalam Production.ProductModel tabel.

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

D. Membuat indeks XML sekunder dengan pemadatan XML

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

Contoh berikut membuat indeks XML sekunder pada CatalogDescription kolom dalam Production.ProductModel tabel.

IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;  
GO  
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH
    WITH (XML_COMPRESSION = ON);
GO

Baca juga