Bagikan melalui

Membuat indeks dengan kolom yang disertakan

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Artikel ini menjelaskan cara menambahkan kolom yang disertakan (atau non-kunci) untuk memperluas fungsionalitas indeks non-kluster di SQL Server dengan menggunakan SQL Server Management Studio atau Transact-SQL. Dengan menyertakan kolom non-kunci, Anda dapat membuat indeks nonclustered yang mencakup lebih banyak kueri. Ini karena kolom non-kunci memiliki manfaat berikut:

  • Mereka bisa menjadi jenis data yang tidak diizinkan sebagai kolom kunci indeks.
  • Kolom tidak dipertimbangkan oleh Mesin Database saat menghitung jumlah kolom kunci indeks atau ukuran kunci indeks.

Indeks dengan kolom non-kunci dapat secara signifikan meningkatkan performa kueri ketika semua kolom dalam kueri disertakan dalam indeks baik sebagai kolom kunci atau non-kunci. Perolehan performa dicapai karena pengoptimal kueri dapat menemukan semua nilai kolom dalam indeks; tabel atau data indeks terkluster tidak diakses sehingga menghasilkan lebih sedikit operasi I/O disk.

Catatan

Saat indeks berisi semua kolom yang dirujuk oleh kueri, indeks biasanya disebut sebagai mencakup kueri.

Rekomendasi Desain

  • Desain ulang indeks nonclustered yang memiliki ukuran kunci indeks besar sehingga hanya kolom yang digunakan untuk pencarian dan pencarian yang merupakan kolom kunci. Buat semua kolom lain yang mencakup kueri ke dalam kolom non-kunci. Dengan cara ini, Anda akan memiliki semua kolom yang diperlukan untuk mencakup kueri, tetapi kunci indeks itu sendiri kecil dan efisien.

  • Sertakan kolom non-kunci dalam indeks non-kluster untuk menghindari melebihi batasan ukuran indeks saat ini dari maksimum 32 kolom kunci dan ukuran kunci indeks maksimum 1.700 byte (16 kolom kunci dan 900 byte sebelum SQL Server 2016 (13.x)). Mesin Database tidak mempertimbangkan kolom non-kunci saat menghitung jumlah kolom kunci indeks atau ukuran kunci indeks.

  • Urutan kolom non-kunci dalam definisi indeks tidak memengaruhi performa kueri yang menggunakan indeks.

  • Hindari indeks non-kluster yang sangat luas di mana kolom yang disertakan tidak mewakili subset kolom tabel yang mendasarinya yang cukup sempit. Jika menambahkan indeks lebar, selalu verifikasi apakah biaya memperbarui satu indeks ekstra lebar mengimbangi biaya pembacaan langsung dari tabel.

Pembatasan dan batasan

  • Kolom non-kunci hanya dapat ditentukan pada indeks nonclustered.

  • Semua jenis data kecuali teks, ntext, dan gambar dapat digunakan sebagai kolom non-kunci.

  • Kolom komputasi yang deterministik dan tepat atau tidak tepat dapat berupa kolom non-kunci. Untuk informasi selengkapnya, lihat Indeks pada kolom komputasi.

  • Kolom komputasi yang berasal dari tipe data gambar, ntext, dan teks dapat berupa kolom non-kunci selama jenis data kolom komputasi diizinkan sebagai kolom indeks non-kunci.

  • Kolom non-kunci tidak dapat dihilangkan dari tabel kecuali indeks tabel tersebut dihilangkan terlebih dahulu.

  • Kolom non-kunci tidak dapat diubah, kecuali untuk melakukan hal berikut:

    • Ubah nullability kolom dari NOT NULL ke NULL.

    • Tingkatkan panjang kolom varchar, nvarchar, atau varbinary .

Keamanan

Izin

Memerlukan izin ALTER pada tabel atau tampilan. Pengguna harus menjadi anggota peran server tetap sysadmin atau peran database tetap db_ddladmin dan db_owner .

Menggunakan SQL Server Management Studio untuk membuat indeks dengan kolom non-kunci

  1. Di Object Explorer, pilih tanda plus untuk memperluas database yang berisi tabel tempat Anda ingin membuat indeks dengan kolom non-kunci.

  2. Pilih tanda plus untuk memperluas folder Tabel .

  3. Pilih tanda plus untuk memperluas tabel tempat Anda ingin membuat indeks dengan kolom non-kunci.

  4. Klik kanan folder Indeks, arahkan ke Indeks Baru, dan pilih Indeks Non-Kluster....

  5. Dalam kotak dialog Indeks Baru, pada halaman Umum , masukkan nama indeks baru dalam kotak Nama indeks.

  6. Di bawah tab Kolom kunci indeks, pilih Tambahkan....

  7. Dalam kotak dialog Pilih Kolom dari table_name , pilih kotak centang atau kotak centang kolom tabel atau kolom yang akan ditambahkan ke indeks.

  8. Pilih OK.

  9. Di bawah tab Kolom yang disertakan , pilih Tambahkan....

  10. Dalam kotak dialog Pilih Kolom daritable_name , pilih kotak centang atau kotak centang kolom tabel atau kolom yang akan ditambahkan ke indeks sebagai kolom non-kunci.

  11. Pilih OK.

  12. Dalam kotak dialog Indeks Baru, pilih OK.

Menggunakan Transact-SQL untuk membuat indeks dengan kolom non-kunci

  1. Di Object Explorer, sambungkan ke instans Mesin Database.

  2. Pada bilah Standar, pilih Kueri Baru.

  3. Salin dan tempel contoh berikut ke dalam jendela kueri dan pilih Jalankan.

    USE AdventureWorks2022;
GO
-- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.
-- index key column is PostalCode and the nonkey columns are
-- AddressLine1, AddressLine2, City, and StateProvinceID.
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

Konten terkait