Menonaktifkan indeks dan batasan

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel 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.

Batasan

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 Anda menonaktifkan indeks unik, PRIMARY KEY atau UNIQUE batasan dan semua FOREIGN KEY batasan yang mereferensikan kolom terindeks dari tabel lain juga dinonaktifkan. Saat Anda menonaktifkan indeks berkluster, semua batasan masuk dan keluar FOREIGN KEY pada tabel yang mendasar juga dinonaktifkan. Nama batasan tercantum dalam pesan peringatan saat indeks dinonaktifkan. Setelah Anda membangun ulang indeks, semua batasan harus diaktifkan secara manual dengan menggunakan ALTER TABLE CHECK CONSTRAINT pernyataan .

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

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 terpisah ALTER INDEX ALL REBUILD .

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 indeks tersebut pada tabel yang direferensikan.

Baris data indeks terkluster 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 ALTER INDEX REBUILD pernyataan atau CREATE INDEX WITH DROP_EXISTING . Untuk informasi selengkapnya tentang operasi indeks online, lihat Melakukan operasi indeks secara online.

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

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

  • AUTO_CREATE_STATISTICS diatur ke ON.
  • 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; sebagai gantinya, 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.

Jika tabel adalah tumpukan, semua indeks nonclustered dibangun kembali.

Izin

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

Menggunakan SQL Server Management Studio

Menonaktifkan indeks

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

  2. Pilih tanda plus untuk memperluas folder Tabel .

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

  4. Pilih 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.

  6. Dalam kotak dialog Nonaktifkan Indeks , verifikasi bahwa indeks yang benar ada di Indeks untuk menonaktifkan kisi dan pilih OK.

Menonaktifkan semua indeks pada tabel

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

  2. Pilih tanda plus untuk memperluas folder Tabel .

  3. Pilih 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 di Indeks untuk menonaktifkan kisi dan pilih 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.

  • Keadaan

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

    • Kosong

      Sebelum eksekusi, Status kosong.

    • Dalam proses

      Menonaktifkan indeks telah dimulai tetapi tidak selesai.

    • Sukses

      Operasi nonaktifkan berhasil diselesaikan.

    • Kesalahan

      Terjadi kesalahan selama operasi nonaktifkan 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.
    • Pilih hyperlink untuk menampilkan kotak dialog yang menampilkan kesalahan penuh.

Menggunakan Transact-SQL

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

Menonaktifkan indeks

  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. Sampel ini menonaktifkan IX_Employee_OrganizationLevel_OrganizationNode indeks pada HumanResources.Employee tabel.

    USE AdventureWorks2022;
GO

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode
    ON HumanResources.Employee
DISABLE;

Menonaktifkan semua indeks pada tabel

  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. Sampel ini menonaktifkan semua indeks pada HumanResources.Employee tabel.

    USE AdventureWorks2022;
GO

ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

Konten terkait