Nonaktifkan Indeks dan Batasan

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Topik ini menjelaskan cara menonaktifkan indeks atau batasan di SQL Server dengan menggunakan SQL Server Management Studio atau Transact-SQL. Menonaktifkan indeks mencegah akses pengguna ke indeks, dan untuk indeks berkluster ke data tabel yang mendasar. Definisi indeks tetap dalam metadata, dan statistik indeks disimpan pada indeks nonclustered. Menonaktifkan indeks berkluster pada tampilan atau indeks non-kluster menghapus data indeks secara fisik. Menonaktifkan indeks berkluster pada tabel mencegah akses ke data; data masih tetap dalam tabel, tetapi tidak tersedia untuk operasi bahasa manipulasi data (DML) hingga indeks dihilangkan atau dibangun kembali.

Dalam Topik Ini

Sebelum Anda mulai

Batasan dan Pembatasan

  • Indeks tidak dipertahankan saat dinonaktifkan.

  • Pengoptimal kueri tidak mempertimbangkan indeks yang dinonaktifkan saat membuat rencana eksekusi kueri. Selain itu, kueri yang mereferensikan indeks yang dinonaktifkan dengan petunjuk tabel gagal.

  • Anda tidak dapat membuat indeks yang menggunakan nama yang sama dengan indeks yang sudah dinonaktifkan.

  • Indeks yang dinonaktifkan dapat dihilangkan.

  • Saat menonaktifkan indeks unik, batasan KUNCI PRIMER atau UNIK dan semua batasan KUNCI ASING yang mereferensikan kolom terindeks dari tabel lain juga dinonaktifkan. Saat menonaktifkan indeks berkluster, semua batasan KUNCI ASING masuk dan keluar pada tabel yang mendasar juga dinonaktifkan. Nama batasan tercantum dalam pesan peringatan saat indeks dinonaktifkan. Setelah membangun ulang indeks, semua batasan harus diaktifkan secara manual dengan menggunakan pernyataan ALTER TABLE CHECK CONSTRAINT.

  • Indeks non-kluster secara otomatis dinonaktifkan saat indeks terkluster terkait dinonaktifkan. Mereka tidak dapat diaktifkan sampai indeks berkluster pada tabel atau tampilan diaktifkan atau indeks berkluster pada tabel dihilangkan. Indeks non-kluster harus diaktifkan secara eksplisit, kecuali indeks berkluster diaktifkan dengan menggunakan pernyataan ALTER INDEX ALL REBUILD.

  • Pernyataan ALTER INDEX ALL REBUILD membangun kembali dan mengaktifkan semua indeks yang dinonaktifkan pada tabel, kecuali untuk indeks yang dinonaktifkan pada tampilan. Indeks pada tampilan harus diaktifkan dalam pernyataan ALTER INDEX ALL REBUILD terpisah.

  • Menonaktifkan indeks berkluster pada tabel juga menonaktifkan semua indeks terkluster dan non-kluster pada tampilan yang mereferensikan tabel tersebut. Indeks ini harus dibangun kembali sama seperti yang ada pada tabel yang direferensikan.

  • Baris data indeks berkluster yang dinonaktifkan tidak dapat diakses kecuali untuk menghilangkan atau membangun kembali indeks berkluster.

  • Anda dapat membangun kembali indeks non-kluster yang dinonaktifkan secara online ketika tabel tidak memiliki indeks berkluster yang dinonaktifkan. Namun, Anda harus selalu membangun kembali indeks terkluster yang dinonaktifkan secara offline jika Anda menggunakan pernyataan ALTER INDEX REBUILD atau CREATE INDEX WITH DROP_EXISTING. Untuk informasi selengkapnya tentang operasi indeks online, lihat Melakukan Operasi Indeks Online.

  • Pernyataan CREATE STATISTICS tidak dapat berhasil dijalankan pada tabel yang memiliki indeks berkluster yang dinonaktifkan.

  • Opsi database AUTO_CREATE_STATISTICS membuat statistik baru pada kolom saat indeks dinonaktifkan dan kondisi berikut ada:

    • AUTO_CREATE_STATISTICS diatur ke AKTIF

    • Tidak ada statistik yang ada untuk kolom tersebut.

    • Statistik diperlukan selama pengoptimalan kueri.

  • Jika indeks berkluster dinonaktifkan, DBCC CHECKDB tidak dapat mengembalikan informasi tentang tabel yang mendasar; sebaliknya, pernyataan melaporkan bahwa indeks berkluster dinonaktifkan. DBCC INDEXDEFRAG tidak dapat digunakan untuk mendefragmentasi indeks yang dinonaktifkan; pernyataan gagal dengan pesan kesalahan. Anda dapat menggunakan DBCC DBREINDEX untuk membangun kembali indeks yang dinonaktifkan.

  • Membuat indeks berkluster baru memungkinkan indeks nonclustered yang dinonaktifkan sebelumnya. Untuk informasi selengkapnya, lihat Mengaktifkan Indeks dan Batasan.

Keamanan

Izin

Untuk menjalankan ALTER INDEX, minimal, diperlukan izin UBAH pada tabel atau tampilan.

Menggunakan SQL Server Management Studio

Untuk menonaktifkan indeks

  1. Di Object Explorer, klik tanda plus untuk memperluas database yang berisi tabel tempat Anda ingin menonaktifkan indeks.

  2. Klik tanda plus untuk memperluas folder Tabel .

  3. Klik tanda plus untuk memperluas tabel tempat Anda ingin menonaktifkan indeks.

  4. Klik tanda plus untuk memperluas folder Indeks .

  5. Klik kanan indeks yang ingin Anda nonaktifkan dan pilih Nonaktifkan.

Catatan

Jika tabel terbuka dalam mode Desain , kontrol Nonaktifkan tidak tersedia. Untuk melanjutkan, tutup perancang tabel dan mulai lagi.

  1. Dalam kotak dialog Nonaktifkan Indeks , verifikasi bahwa indeks yang benar ada dalam Indeks untuk menonaktifkan kisi dan klik OK.

Untuk menonaktifkan semua indeks pada tabel

  1. Di Object Explorer, klik tanda plus untuk memperluas database yang berisi tabel tempat Anda ingin menonaktifkan indeks.

  2. Klik tanda plus untuk memperluas folder Tabel .

  3. Klik tanda plus untuk memperluas tabel tempat Anda ingin menonaktifkan indeks.

  4. Klik kanan folder Indeks dan pilih Nonaktifkan Semua.

  5. Dalam kotak dialog Nonaktifkan Indeks , verifikasi bahwa indeks yang benar ada dalam Indeks untuk menonaktifkan kisi dan klik OK. Untuk menghapus indeks dari Indeks untuk menonaktifkan kisi, pilih indeks lalu tekan tombol Hapus.

Informasi berikut ini tersedia dalam kotak dialog Nonaktifkan Indeks :

Nama Indeks
Menampilkan nama indeks. Selama eksekusi, kolom ini juga menampilkan ikon yang mewakili status.

Nama Tabel
Menampilkan nama tabel atau tampilan tempat indeks dibuat.

Tipe Indeks
Menampilkan jenis indeks: Berkluster, Tidak Terkluster, Spasial, atau XML.

Status
Menampilkan status operasi penonaktifan. Nilai yang mungkin setelah eksekusi adalah:

  • Blank

    Sebelum Status eksekusi kosong.

  • Dalam proses

    Pennonaktifkan indeks telah dimulai tetapi tidak selesai.

  • Sukses

    Operasi nonaktifkan berhasil diselesaikan.

  • Kesalahan

    Terjadi kesalahan selama operasi penonaktifan indeks, dan operasi tidak berhasil diselesaikan.

  • Berhenti

    Penonaktifan indeks tidak berhasil diselesaikan karena pengguna menghentikan operasi.

Pesan
Menyediakan teks pesan kesalahan selama operasi nonaktifkan. Selama eksekusi, kesalahan muncul sebagai hyperlink. Teks hyperlink menjelaskan isi kesalahan. Kolom Pesan jarang cukup lebar untuk membaca teks pesan lengkap. Ada dua cara untuk mendapatkan teks lengkap:

  • Pindahkan penunjuk mouse ke sel pesan untuk menampilkan TipsAlat dengan teks kesalahan.

  • Klik hyperlink untuk menampilkan kotak dialog yang menampilkan kesalahan penuh.

Menggunakan T-SQL

Untuk menonaktifkan indeks

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

  2. Pada bilah Standar, klik Kueri Baru.

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

    USE AdventureWorks2022;  
GO  
-- disables the IX_Employee_OrganizationLevel_OrganizationNode index  
-- on the HumanResources.Employee table  
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
DISABLE;

Untuk menonaktifkan semua indeks pada tabel

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

  2. Pada bilah Standar, klik Kueri Baru.

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

    USE AdventureWorks2022;  
GO  
-- Disables all indexes on the HumanResources.Employee table.  
ALTER INDEX ALL ON HumanResources.Employee  
DISABLE;

Untuk informasi selengkapnya, lihat ALTER INDEX (T-SQL).