SQL DROP INDEX Statement
Last Updated :
18 Mar, 2024
In SQL, we use the CREATE INDEX statement to create an index and retrieve data from a database speedily. So after we have created an index sometimes we need to drop that index because dropping and then recreating the index boosts data insertion speed.
It is necessary to create an index first and only then we can drop that index. In this article, we will learn how to drop an index in SQL using DROP INDEX Statement with examples.
DROP INDEX Statement
The DROP INDEX Command is used to remove an index from the table. Indexes improve the speed of searches/queries but they also occupy space, which can cause extra time consumption on table modification operations.
- Removing unused indexes can improve INSERT, UPDATE, and DELETE operations on the table.
- It will also free up some space.
- Deleting an index can have a significant impact on database queries therefore only drop the unused or unrequired indexes.
Note: Indexes created by PRIMARY KEY or UNIQUE constraint can not be deleted with just a DROP INDEX statement. To delete such indexes, we need to first drop the constraints using the ALTER TABLE statement, and then drop the index.
Syntax
DROP INDEX Statement differs in different database systems, for example
MySQL
ALTER TABLE table_name DROP INDEX index_name;
MS Access
DROP INDEX index_name ON table_name;
SQL Server
DROP INDEX table_name.index_name;
DB2/Oracle
 DROP INDEX index_name;
PostgreSQL
DROP INDEX index_name;
Creating an Index on Table
Before learning how to drop an index we need to create an index. Let’s create a table and add index using the CREATE INDEX Statement. We will be using MySQL database in the examples.Â
MySQL
CREATE DATABASE GEEKSFORGEEKS;
USE GEEKSFORGEEKS;
CREATE TABLE EMPLOYEE(
EMP_ID INT,
EMP_NAME VARCHAR(20),
AGE INT,
DOB DATE,
SALARY DECIMAL(7,2));
CREATE INDEX EMP
ON EMPLOYEE(EMP_ID,EMP_NAME);
Output:
Creating an index on two columns
SQL DROP INDEX Statement Example
Let’s cover examples of DROP INDEX statement and understand its workings in SQL. We will learn different use cases of the DROP INDEX statement in these examples.
We can drop the index using two ways either with IF EXISTS or with ALTER TABLE so we will first drop the index using if exists.
Example 1: Dropping the index with IF EXISTS
DROP INDEX IF EXISTS EMP ON EMPLOYEE;
Dropping index
Output
Since there are no indexes in the database with the supplied name, the aforementioned query simply ends execution without returning any errors.
Commands Excuted Successfully;
Example 2: Dropping the index with ALTER TABLE in SQL
ALTER TABLE EMPLOYEE
DROP INDEX EMP;
Output
Dropping the index
Verifying DROP INDEX Statement
To verify if the DROP INDEX statement has successfully removed the index from the table, we can check the indexes on the table. If the index is not present in the list, we know it has been deleted.
Syntax
The syntax for viewing index on a table differ for different databases, for example:
SQL Server:
SELECT * FROM sys.indexes WHERE object_id = (SELECT object_id FROM sys.objects WHERE name = 'YOUR_TABLE_NAME')
MySQL:
SHOW INDEXES FROM YOUR_TABLE_NAME;
PostgreSQL:
SELECT * FROM USER_INDEXES;
Oracle:
SELECT
indexname, indexdef
FROM
pg_indexes
WHERE
tablename = 'your_table_name';
Conclusion
In summary, the SQL DROP INDEX statement is used to remove an index from a database table, providing greater control over index management and potentially improving database performance by reducing index maintenance overhead.
SQL DROP INDEX Statement – FAQs
How to create an index in SQL?
To create an index in MYSQL we use the CREATE INDEX command.
How to drop an index in SQL?
To drop an index in SQL we use the ALTER TABLE DROP INDEX command.
What is the need to drop an index?
Generally we drop an index and then recreate it because it increases the data insertion speed.
Share your thoughts in the comments
Please Login to comment...