DROP INDEX (SQL Bertransaksi)

Artikel
06/24/2022
13 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Menghapus satu atau beberapa indeks relasional, spasial, terfilter, atau XML dari database saat ini. Anda dapat menghilangkan indeks berkluster dan memindahkan tabel yang dihasilkan ke grup file atau skema partisi lain dalam satu transaksi dengan menentukan opsi PINDAHKAN KE.

Pernyataan DROP INDEX tidak berlaku untuk indeks yang dibuat dengan menentukan batasan KUNCI PRIMER atau UNIK. Untuk menghapus batasan dan indeks terkait, gunakan ALTER TABLE dengan klausa DROP CONSTRAINT.

Penting

Sintaks yang ditentukan dalam <drop_backward_compatible_index> akan dihapus dalam versi MicrosoftSQL Server yang akan datang. Hindari menggunakan sintaks ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur tersebut. Gunakan sintaks yang ditentukan di bawah <drop_relational_or_xml_index> sebagai gantinya. Indeks XML tidak dapat dihilangkan menggunakan sintaksis kompatibel mundur.

Topic link icon Konvensi Sintaksis T-SQL

Sintaks

-- Syntax for SQL Server (All options except filegroup and filestream apply to Azure SQL Database.)  
  
DROP INDEX [ IF EXISTS ]   
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ]   
| <drop_backward_compatible_index> [ ,...n ]  
}  
  
<drop_relational_or_xml_or_spatial_index> ::=  
    index_name ON <object>   
    [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]  
  
<drop_backward_compatible_index> ::=  
    [ owner_name. ] table_or_view_name.index_name  
  
<object> ::=  
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }  
  
<drop_clustered_index_option> ::=  
{  
    MAXDOP = max_degree_of_parallelism  
  | ONLINE = { ON | OFF }  
  | MOVE TO { partition_scheme_name ( column_name )   
            | filegroup_name  
            | "default"   
            }  
  [ FILESTREAM_ON { partition_scheme_name   
            | filestream_filegroup_name   
            | "default" } ]  
}

-- Syntax for Azure SQL Database  
  
DROP INDEX  
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ]   
}  
  
<drop_relational_or_xml_or_spatial_index> ::=   
    index_name ON <object>  
  
<object> ::=   
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]

Catatan

Untuk melihat sintaks transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

IF EXISTS
Berlaku untuk: SQL Server ( SQL Server 2016 (13.x) melalui versi saat ini).

Secara kondisional menghilangkan indeks hanya jika sudah ada.

index_name
Adalah nama indeks yang akan dihilangkan.

database_name
Adalah nama database.

nama_skema
Adalah nama skema tempat tabel atau tampilan berada.

table_or_view_name
Adalah nama tabel atau tampilan yang terkait dengan indeks. Indeks spasial hanya didukung pada tabel.

Untuk menampilkan laporan indeks pada objek, gunakan tampilan katalog sys.indexes .

Azure SQL Database mendukung format nama tiga bagian database_name. [schema_name].object_name ketika database_name adalah database saat ini atau database_name adalah tempdb dan object_name dimulai dengan #.

<drop_clustered_index_option>
Berlaku untuk: SQL Server 2008 dan yang lebih baru, SQL Database.

Mengontrol opsi indeks berkluster. Opsi ini tidak dapat digunakan dengan jenis indeks lainnya.

MAXDOP = max_degree_of_parallelism
Berlaku untuk: SQL Server 2008 dan yang lebih baru, SQL Database (hanya Tingkat Performa P2 dan P3).

Mengambil alih tingkat maksimum opsi konfigurasi paralelisme selama durasi operasi indeks. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme. Gunakan MAXDOP untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.

Penting

MAXDOP tidak diizinkan untuk indeks spasial atau indeks XML.

max_degree_of_parallelism dapat berupa:

1
Menekan pembuatan rencana paralel.

>1
Membatasi jumlah maksimum prosesor yang digunakan dalam operasi indeks paralel ke angka yang ditentukan.

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 SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan Fitur yang Didukung untuk SQL Server 2016.

ONLINE = ON | OFF
Berlaku untuk: SQL Server 2008 dan yang lebih baru, Azure SQL Database.

Menentukan apakah tabel yang mendasari dan indeks terkait tersedia untuk kueri dan modifikasi data selama operasi indeks. Defaultnya adalah NONAKTIF.

AKTIF
Kunci tabel jangka panjang tidak ditahan. Ini memungkinkan kueri atau pembaruan pada tabel yang mendasarinya untuk dilanjutkan.

TIDAK AKTIF
Kunci tabel diterapkan dan tabel tidak tersedia selama durasi operasi indeks.

Opsi ONLINE hanya dapat ditentukan saat Anda menghilangkan indeks berkluster. Untuk informasi selengkapnya, lihat bagian Keterangan.

Catatan

Operasi indeks online tidak tersedia di setiap edisi SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan Fitur yang Didukung untuk SQL Server 2016.

PINDAH KE { partition_scheme_name(column_name) | filegroup_name | "default"
Berlaku untuk: SQL Server 2008 dan yang lebih baru. SQL Database mendukung "default" sebagai nama grup file.

Menentukan lokasi untuk memindahkan baris data yang saat ini berada di tingkat daun indeks berkluster. Data dipindahkan ke lokasi baru dalam bentuk tumpukan. Anda dapat menentukan skema partisi atau grup file sebagai lokasi baru, tetapi skema partisi atau grup file harus sudah ada. MOVE TO tidak valid untuk tampilan terindeks atau indeks non-kluster. Jika skema partisi atau grup file tidak ditentukan, tabel yang dihasilkan akan terletak di skema partisi atau grup file yang sama seperti yang didefinisikan untuk indeks berkluster.

Jika indeks berkluster dihilangkan dengan menggunakan MOVE TO, indeks non-kluster pada tabel dasar akan dibangun kembali, tetapi indeks tersebut tetap berada dalam grup file atau skema partisi aslinya. Jika tabel dasar dipindahkan ke grup file atau skema partisi yang berbeda, indeks non-kluster tidak dipindahkan bertepatan dengan lokasi baru tabel dasar (heap). Oleh karena itu, bahkan jika indeks non-kluster sebelumnya selaras dengan indeks berkluster, indeks tersebut mungkin tidak lagi selaras dengan tumpukan. Untuk informasi selengkapnya tentang perataan indeks yang dipartisi, lihat Tabel dan Indeks yang Dipartisi.

partition_scheme_name(column_name)
Berlaku untuk: SQL Server 2008 dan yang lebih baru, SQL Database.

Menentukan skema partisi sebagai lokasi untuk tabel yang dihasilkan. Skema partisi harus sudah dibuat dengan mengeksekusi CREATE PARTITION SCHEME atau ALTER PARTITION SCHEME. Jika tidak ada lokasi yang ditentukan dan tabel dipartisi, tabel disertakan dalam skema partisi yang sama dengan indeks berkluster yang ada.

Nama kolom dalam skema tidak dibatasi untuk kolom dalam definisi indeks. Kolom apa pun dalam tabel dasar dapat ditentukan.

filegroup_name
Berlaku untuk: SQL Server 2008 dan yang lebih baru.

Menentukan grup file sebagai lokasi untuk tabel yang dihasilkan. Jika tidak ada lokasi yang ditentukan dan tabel tidak dipartisi, tabel yang dihasilkan disertakan dalam grup file yang sama dengan indeks berkluster. Grup file harus sudah ada.

"default"
Menentukan lokasi default untuk tabel yang dihasilkan.

Catatan

Dalam konteks ini, defaultnya bukan kata kunci. Ini adalah pengidentifikasi untuk grup file default dan harus dibatasi, seperti dalam MOVE TO "default" atau MOVE TO [default]. Jika "default" ditentukan, opsi QUOTED_IDENTIFIER harus diatur AKTIF untuk sesi saat ini. Ini adalah pengaturan default. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER (SQL T-SQL).

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
Berlaku untuk: SQL Server 2008 dan yang lebih baru.

Menentukan lokasi untuk memindahkan tabel FILESTREAM yang saat ini berada di tingkat daun indeks berkluster. Data dipindahkan ke lokasi baru dalam bentuk tumpukan. Anda dapat menentukan skema partisi atau grup file sebagai lokasi baru, tetapi skema partisi atau grup file harus sudah ada. FILESTREAM ON tidak valid untuk tampilan terindeks atau indeks non-kluster. Jika skema partisi tidak ditentukan, data akan terletak dalam skema partisi yang sama seperti yang didefinisikan untuk indeks berkluster.

partition_scheme_name
Menentukan skema partisi untuk data FILESTREAM. Skema partisi harus sudah dibuat dengan mengeksekusi CREATE PARTITION SCHEME atau ALTER PARTITION SCHEME. Jika tidak ada lokasi yang ditentukan dan tabel dipartisi, tabel disertakan dalam skema partisi yang sama dengan indeks berkluster yang ada.

Jika Anda menentukan skema partisi untuk MOVE TO, Anda harus menggunakan skema partisi yang sama untuk FILESTREAM ON.

filestream_filegroup_name
Menentukan grup file FILESTREAM untuk data FILESTREAM. Jika tidak ada lokasi yang ditentukan dan tabel tidak dipartisi, data disertakan dalam grup file FILESTREAM default.

"default"
Menentukan lokasi default untuk data FILESTREAM.

Catatan

Dalam konteks ini, default bukan kata kunci. Ini adalah pengidentifikasi untuk grup file default dan harus dibatasi, seperti dalam MOVE TO "default" atau MOVE TO [default]. Jika "default" ditentukan, opsi QUOTED_IDENTIFIER harus AKTIF untuk sesi saat ini. Ini adalah pengaturan default. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER (SQL T-SQL).

Keterangan

Ketika indeks nonkluster dihilangkan, definisi indeks dihapus dari metadata dan halaman data indeks (pohon B) dihapus dari file database. Ketika indeks berkluster dihilangkan, definisi indeks dihapus dari metadata dan baris data yang disimpan dalam tingkat daun indeks berkluster disimpan dalam tabel yang tidak berurutan yang dihasilkan, tumpukan. Semua ruang yang sebelumnya ditempati oleh indeks diperoleh kembali. Ruang ini kemudian dapat digunakan untuk objek database apa pun.

Catatan

SQL Server dokumentasi menggunakan istilah pohon B umumnya mengacu pada indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Tinjau Panduan Arsitektur dan Desain Indeks SQL Server untuk detailnya.

Indeks tidak dapat dihilangkan jika grup file tempatnya berada offline atau diatur ke baca-saja.

Ketika indeks berkluster dari tampilan terindeks dihilangkan, semua indeks non-kluster dan statistik yang dibuat secara otomatis pada tampilan yang sama secara otomatis dihilangkan. Statistik yang dibuat secara manual tidak dihilangkan.

Sintaks table_or_view_name.index_name dipertahankan untuk kompatibilitas mundur. Indeks XML atau indeks spasial tidak dapat dihilangkan dengan menggunakan sintaksis kompatibel mundur.

Ketika indeks dengan tingkat 128 atau lebih dihilangkan, Mesin Database menangguhkan dealokasi halaman aktual, dan kunci terkaitnya, sampai setelah transaksi dilakukan.

Terkadang indeks dihilangkan dan dibuat ulang untuk mengatur ulang atau membangun ulang indeks, seperti menerapkan nilai faktor pengisian baru atau mengatur ulang data setelah pemuatan massal. Untuk melakukan ini, menggunakan ALTER INDEXlebih efisien, terutama untuk indeks berkluster. ALTER INDEX REBUILD memiliki pengoptimalan untuk mencegah overhead membangun kembali indeks non-kluster.

Menggunakan Opsi dengan DROP INDEX

Anda dapat mengatur opsi indeks berikut saat Anda menghilangkan indeks berkluster: MAXDOP, ONLINE, dan MOVE TO.

Gunakan MOVE TO untuk menghilangkan indeks berkluster dan memindahkan tabel yang dihasilkan ke grup file atau skema partisi lain dalam satu transaksi.

Saat Anda menentukan ONLINE = AKTIF, kueri dan modifikasi pada data yang mendasarinya dan indeks non-kluster terkait tidak diblokir oleh transaksi DROP INDEX. Hanya satu indeks berkluster yang dapat dihilangkan secara online pada satu waktu. Untuk deskripsi lengkap opsi ONLINE, lihat CREATE INDEX (Transact-SQL).

Anda tidak dapat menjatuhkan indeks berkluster secara online jika indeks dinonaktifkan pada tampilan, atau berisi teks, ntext, gambar, varchar(max), nvarchar(max), varbinary(max), atau kolom xml di baris data tingkat daun.

Menggunakan opsi ONLINE = ON dan MOVE TO memerlukan ruang disk sementara tambahan.

Setelah indeks dihilangkan, timbunan yang dihasilkan muncul dalam tampilan katalog sys.indexes dengan NULL di kolom nama . Untuk melihat nama tabel, gabungkan sys.indexes dengan sys.tables di object_id. Untuk contoh kueri, lihat contoh D.

Pada komputer multiprosesor yang berjalan SQL Server 2005 Enterprise Edition atau yang lebih baru, DROP INDEX dapat menggunakan lebih banyak prosesor untuk melakukan operasi pemindaian dan pengurutan yang terkait dengan penghapusan indeks berkluster, seperti yang dilakukan kueri lainnya. Anda dapat mengonfigurasi jumlah prosesor yang digunakan untuk menjalankan pernyataan DROP INDEX secara manual dengan menentukan opsi indeks MAXDOP. Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.

Ketika indeks berkluster dihilangkan, partisi heap yang sesuai mempertahankan pengaturan kompresi data mereka kecuali skema partisi dimodifikasi. Jika skema partisi diubah, semua partisi dibangun kembali ke status tidak terkompresi (DATA_COMPRESSION = NONE). Untuk menghilangkan indeks berkluster dan mengubah skema partisi memerlukan dua langkah berikut:

Hilangkan indeks berkluster.
Ubah tabel dengan menggunakan ALTER TABLE ... MEMBANGUN... opsi menentukan opsi pemadatan.

Ketika indeks berkluster dihilangkan OFFLINE, hanya tingkat atas indeks berkluster yang dihapus; oleh karena itu, operasinya cukup cepat. Ketika indeks berkluster dihilangkan ONLINE, SQL Server membangun kembali tumpukan dua kali, sekali untuk langkah 1 dan sekali untuk langkah 2. Untuk informasi selengkapnya tentang kompresi data, lihat Kompresi Data.

Indeks XML

Opsi tidak dapat ditentukan ketika Anda menghilangkan indeks anXML. Selain itu, Anda tidak dapat menggunakan table_or_view_name.index_name sintaksis. Saat indeks XML utama dihilangkan, semua indeks XML sekunder terkait secara otomatis dihilangkan. Untuk informasi selengkapnya, lihat Indeks XML (SQL Server).

Indeks spasial

Indeks spasial hanya didukung pada tabel. Saat Anda menghilangkan indeks spasial, Anda tidak dapat menentukan opsi apa pun atau menggunakan .index_name. Sintaks yang benar adalah sebagai berikut:

DROP INDEX spatial_index_name ON spatial_table_name;

Untuk informasi selengkapnya tentang indeks spasial, lihat Gambaran Umum Indeks Spasial.

Izin

Untuk menjalankan DROP INDEX, minimal, diperlukan izin UBAH pada tabel atau tampilan. Izin ini diberikan secara default ke peran server tetap sysadmin dan peran database tetap db_ddladmin dan db_owner .

Contoh

A. Menghilangkan indeks

Contoh berikut menghapus indeks IX_ProductVendor_VendorID pada ProductVendor tabel dalam database AdventureWorks2012.

DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor;  
GO

B. Menghilangkan beberapa indeks

Contoh berikut menghapus dua indeks dalam satu transaksi dalam database AdventureWorks2012.

DROP INDEX  
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,  
    IX_Address_StateProvinceID ON Person.Address;  
GO

C. Menghilangkan indeks berkluster secara online dan mengatur opsi MAXDOP

Contoh berikut menghapus indeks berkluster dengan opsi diatur ONLINE ke ON dan MAXDOP diatur ke 8. Karena opsi PINDAHKAN KE tidak ditentukan, tabel yang dihasilkan disimpan dalam grup file yang sama dengan indeks. Contoh ini menggunakan database AdventureWorks2012

Berlaku untuk: SQL Server 2008 dan yang lebih baru, SQL Database.

DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate   
    ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);  
GO

D. Menghapus indeks berkluster secara online dan memindahkan tabel ke grup file baru

Contoh berikut menghapus indeks berkluster secara online dan memindahkan tabel yang dihasilkan (heap) ke grup NewGroup file dengan menggunakan MOVE TO klausul . Tampilan sys.indexeskatalog , sys.tables, dan sys.filegroups dikueri untuk memverifikasi penempatan indeks dan tabel di grup file sebelum dan sesudah pemindahan. (Dimulai dengan SQL Server 2016 (13.x) Anda dapat menggunakan sintaks DROP INDEX IF EXISTS.)

Berlaku untuk: SQL Server 2008 dan yang lebih baru.

--Create a clustered index on the PRIMARY filegroup if the index does not exist.  
CREATE UNIQUE CLUSTERED INDEX  
    AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate   
        ON Production.BillOfMaterials (ProductAssemblyID, ComponentID,   
        StartDate)  
    ON 'PRIMARY';  
GO  
-- Verify filegroup location of the clustered index.  
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,  
    i.data_space_id, f.name AS [Filegroup Name]  
FROM sys.indexes AS i  
    JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id  
    JOIN sys.tables as t ON i.object_id = t.object_id  
        AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U')  
GO  
--Create filegroup NewGroup if it does not exist.  
IF NOT EXISTS (SELECT name FROM sys.filegroups  
                WHERE name = N'NewGroup')  
    BEGIN  
    ALTER DATABASE AdventureWorks2012  
        ADD FILEGROUP NewGroup;  
    ALTER DATABASE AdventureWorks2012  
        ADD FILE (NAME = File1,  
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\File1.ndf')  
        TO FILEGROUP NewGroup;  
    END  
GO  
--Verify new filegroup  
SELECT * from sys.filegroups;  
GO  
-- Drop the clustered index and move the BillOfMaterials table to  
-- the Newgroup filegroup.  
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.  
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate   
    ON Production.BillOfMaterials   
    WITH (ONLINE = ON, MOVE TO NewGroup);  
GO  
-- Verify filegroup location of the moved table.  
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,  
    i.data_space_id, f.name AS [Filegroup Name]  
FROM sys.indexes AS i  
    JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id  
    JOIN sys.tables as t ON i.object_id = t.object_id  
        AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U');  
GO

E. Menghilangkan batasan KUNCI PRIMER secara online

Indeks yang dibuat sebagai hasil dari pembuatan KUNCI PRIMER atau batasan UNIK tidak dapat dihilangkan dengan menggunakan DROP INDEX. Mereka dihilangkan menggunakan pernyataan ALTER TABLE DROP CONSTRAINT. Untuk informasi selengkapnya, lihat ALTER TABLE.

Contoh berikut menghapus indeks berkluster dengan batasan KUNCI PRIMER dengan menghilangkan batasan. Tabel ProductCostHistory tidak memiliki batasan KUNCI ASING. Jika ya, batasan tersebut harus dihapus terlebih dahulu.

-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.  
ALTER TABLE Production.TransactionHistoryArchive  
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID  
WITH (ONLINE = ON);

F. Menjatuhkan indeks XML

Contoh berikut menghilangkan indeks XML pada ProductModel tabel dalam database AdventureWorks2012.

DROP INDEX PXML_ProductModel_CatalogDescription   
    ON Production.ProductModel;

G. Menghilangkan indeks berkluster pada tabel FILESTREAM

Contoh berikut menghapus indeks berkluster secara online dan memindahkan data tabel (heap) dan FILESTREAM yang dihasilkan ke MyPartitionScheme skema partisi dengan menggunakan MOVE TO klausul dan FILESTREAM ON klausul .

Berlaku untuk: SQL Server 2008 dan yang lebih baru.

DROP INDEX PK_MyClusteredIndex   
    ON dbo.MyTable   
    WITH (MOVE TO MyPartitionScheme,  
          FILESTREAM_ON MyPartitionScheme);  
GO

Lihat juga

ALTER INDEX (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)
EVENTDATA (SQL Transaksi)
sys.indexes (Transact-SQL)
sys.tables (Transact-SQL)
sys.filegroups (Transact-SQL)
sp_spaceused (SQL Bertransaksi)