DROP INDEX (Transact-SQL)
Berlaku untuk: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics Analytics
Platform System (PDW)
Menghapus satu atau beberapa indeks relasional, spasial, difilter, 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 MOVE TO
opsi .
Pernyataan DROP INDEX
tidak berlaku untuk indeks yang dibuat dengan menentukan PRIMARY KEY
atau UNIQUE
membatasi. Untuk menghapus batasan dan indeks terkait, gunakan ALTER TABLE dengan DROP CONSTRAINT
klausa.
Penting
Sintaks yang ditentukan dalam <drop_backward_compatible_index>
akan dihapus dalam versi SQL 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_or_spatial_index>
sebagai gantinya. Indeks XML tidak dapat dihilangkan menggunakan sintaksis kompatibel mundur.
Sintaks
Sintaks untuk SQL Server (semua opsi kecuali grup file dan aliran file berlaku untuk 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" } ]
}
Sintaks untuk 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 }
Sintaks untuk Azure Synapse Analytics dan Analytics Platform System (PDW).
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 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
JIKA ADA
Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru.
Secara kondisional menghilangkan indeks hanya jika sudah ada.
index_name
Nama indeks yang akan dihilangkan.
database_name
Nama database.
schema_name
Nama skema tempat tabel atau tampilan berada.
table_or_view_name
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 (10.0.x) dan versi 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 (10.0.x) dan versi yang lebih baru, SQL Database (hanya Tingkat Performa P2 dan P3).
Mengambil alih tingkat maksimum opsi konfigurasi paralelisme selama operasi indeks. Untuk informasi selengkapnya, lihat Mengonfigurasi tingkat paralelisme maksimum (opsi konfigurasi server). Gunakan MAXDOP
untuk membatasi jumlah prosesor yang digunakan dalam eksekusi rencana paralel. Maksimum adalah 64 prosesor.
Penting
MAXDOP
tidak diperbolehkan untuk indeks spasial atau indeks XML.
max_degree_of_parallelism bisa menjadi salah satu nilai berikut.
Nilai | Deskripsi |
---|---|
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 SQL Server 2022.
ONLINE = AKTIF | OFF
Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru, Azure SQL Database.
Menentukan apakah tabel yang mendasari dan indeks terkait tersedia untuk kueri dan modifikasi data selama operasi indeks. Default adalah OFF
.
ON
: Kunci tabel jangka panjang tidak ditahan. Ini memungkinkan kueri atau pembaruan pada tabel yang mendasarinya untuk melanjutkan.OFF
: Kunci tabel diterapkan dan tabel tidak tersedia selama 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 SQL Server 2022.
PINDAH KE { partition_scheme_name ( column_name ) | filegroup_name | "default" }
Berlaku untuk: SQL Server 2008 (10.0.x) dan versi 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 timbunan. 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 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 apa pun pada tabel dasar 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 nonclustered 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 (10.0.x) dan versi yang lebih baru, SQL Database.
Menentukan skema partisi sebagai lokasi untuk tabel yang dihasilkan. Skema partisi harus sudah dibuat, dengan menjalankan 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 (10.0.x) dan versi 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, 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, QUOTED_IDENTIFIER
opsi harus diatur ON
untuk sesi saat ini. Ini adalah pengaturan default. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER.
FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
Berlaku untuk: SQL Server 2008 (10.0.x) dan versi 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 timbunan. 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 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 menjalankan 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, QUOTED_IDENTIFIER
opsi harus AKTIF untuk sesi saat ini. Ini adalah pengaturan default. Untuk informasi selengkapnya, lihat MENGATUR QUOTED_IDENTIFIER.
Keterangan
Ketika indeks nonclustered 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 diurutkan yang dihasilkan, tumpukan. Semua ruang yang sebelumnya ditempati oleh indeks diperoleh kembali. Ruang ini kemudian dapat digunakan untuk objek database apa pun.
Catatan
Dokumentasi SQL Server menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.
Indeks tidak dapat dihilangkan jika grup file tempat indeks berada offline atau diatur ke baca-saja.
Ketika indeks terkluster dari tampilan terindeks dihilangkan, semua indeks non-kluster dan statistik yang dibuat 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 yang kompatibel mundur.
Ketika indeks dengan 128 tingkat atau lebih dihilangkan, Mesin Database menumpahkan alokasi 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 untuk mengatur ulang data setelah pemuatan massal. Untuk melakukan ini, menggunakan ALTER INDEX lebih efisien, terutama untuk indeks berkluster. ALTER INDEX REBUILD
memiliki pengoptimalan untuk mencegah overhead membangun kembali indeks yang tidak terkluster.
Gunakan opsi dengan DROP INDEX
Anda dapat mengatur opsi indeks berikut saat menghapus 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 = ON
, kueri, dan modifikasi pada data yang mendasarinya dan indeks noncluster terkait tidak diblokir oleh DROP INDEX
transaksi. Hanya satu indeks berkluster yang dapat dihilangkan secara online pada satu waktu. Untuk deskripsi ONLINE
lengkap opsi, lihat MEMBUAT INDEKS.
Anda tidak dapat menjatuhkan indeks berkluster secara online jika indeks dinonaktifkan pada tampilan, atau berisi teks, ntext, gambar, varchar(maks), nvarchar(maks), varbinary(maks), atau kolom xml di baris data tingkat daun.
ONLINE = ON
Menggunakan opsi dan MOVE TO
memerlukan lebih banyak ruang disk sementara.
Setelah indeks dihilangkan, timbunan yang dihasilkan muncul dalam sys.indexes
tampilan katalog dengan NULL
di name
kolom . Untuk melihat nama tabel, gabungkan sys.indexes
dengan sys.tables
pada object_id
. Untuk contoh kueri, lihat contoh D.
Pada komputer multiprosesor yang menjalankan edisi SQL Server 2005 Enterprise atau yang lebih baru, DROP INDEX
mungkin 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 DROP INDEX
pernyataan secara manual dengan menentukan MAXDOP
opsi indeks. Untuk informasi selengkapnya, lihat Mengonfigurasi Operasi Indeks Paralel.
Ketika indeks berkluster dihilangkan, partisi tumpukan 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 ... REBUILD ...
opsi yang menentukan opsi pemadatan.
Ketika indeks berkluster dihilangkan OFFLINE
, hanya tingkat atas indeks berkluster yang dihapus; oleh karena itu, operasinya 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 Pemadatan data.
Indeks XML
Opsi tidak dapat ditentukan saat Anda menghilangkan indeks anXML. Selain itu, Anda tidak dapat menggunakan sintaks.<table_or_view_name>.<index_name>
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, ALTER
izin pada tabel atau tampilan diperlukan. Izin ini diberikan secara default ke peran server tetap sysadmin dan peran database tetap db_ddladmin dan db_owner .
Contoh
Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022
database sampel atau AdventureWorksDW2022
, yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.
J. Menghapus indeks
Contoh berikut menghapus indeks IX_ProductVendor_BusinessEntityID
pada ProductVendor
tabel dalam database AdventureWorks2022.
DROP INDEX IX_ProductVendor_BusinessEntityID
ON Purchasing.ProductVendor;
GO
B. Menghilangkan beberapa indeks
Contoh berikut menghapus dua indeks dalam satu transaksi dalam database AdventureWorks2022.
DROP INDEX
IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
IX_Address_StateProvinceID ON Person.Address;
GO
C. Hilangkan indeks berkluster secara online dan atur opsi MAXDOP
Contoh berikut menghapus indeks berkluster dengan opsi diatur ONLINE
ke ON
dan MAXDOP
diatur ke 8
. MOVE TO
Karena opsi tidak ditentukan, tabel yang dihasilkan disimpan dalam grup file yang sama dengan indeks.
Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru, SQL Database.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO
D. Hilangkan indeks berkluster secara online dan pindahkan tabel ke grup file baru
Contoh berikut menghapus indeks berkluster secara online dan memindahkan tabel yang dihasilkan (tumpukan) ke grup NewGroup
file dengan menggunakan MOVE TO
klausul . Tampilan sys.indexes
katalog , 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 (10.0.x) dan versi 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 AdventureWorks2022
ADD FILEGROUP NewGroup;
ALTER DATABASE AdventureWorks2022
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. Menghapus batasan KUNCI PRIMER secara online
Indeks yang dibuat sebagai akibat dari pembuatan PRIMARY KEY
atau UNIQUE
batasan 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 dengan PRIMARY KEY
menghilangkan batasan. Tabel ProductCostHistory
tidak FOREIGN KEY
memiliki batasan. 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. Menghapus indeks XML
Contoh berikut menghilangkan indeks XML pada ProductModel
tabel dalam database AdventureWorks2022.
DROP INDEX PXML_ProductModel_CatalogDescription
ON Production.ProductModel;
G. Menghapus 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
klausa.
Berlaku untuk: SQL Server 2008 (10.0.x) dan versi yang lebih baru.
DROP INDEX PK_MyClusteredIndex
ON dbo.MyTable
WITH (MOVE TO MyPartitionScheme,
FILESTREAM_ON MyPartitionScheme);
GO
Konten terkait
- ALTER INDEX (Transact-SQL)
- MENGUBAH SKEMA PARTISI (Transact-SQL)
- ALTER TABLE (Transact-SQL)
- BUAT INDEKS (Transact-SQL)
- MEMBUAT SKEMA PARTISI (Transact-SQL)
- MEMBUAT INDEKS SPASIAL (Transact-SQL)
- BUAT INDEKS XML (Transact-SQL)
- EVENTDATA (Transact-SQL)
- sys.indexes
- sys.tables
- sys.filegroups
- sp_spaceused