SQL - Drop index

---

Drop Index Statement

The DROP statement in SQL is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, it will remove them permanently along with their associated data.

Therefore, to drop an existing index from a table in the database, the DROP INDEX command in SQL is used. To use the DROP INDEX command, you need to specify the name of the particular index that you want to delete along with the table name that the index is associated with. Once you execute the command, the index will be removed from the table.

It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required.

Note − We cannot delete the indexes created by PRIMARY KEY or UNIQUE constraints.

Syntax

Following is the syntax of the DROP INDEX command in SQL −

DROP INDEX index_name
ON table_name;

Here,

• index_name is the name of the index that you want to drop.
• table_name is the name of the table that the index is associated with.

Example

First of all, let us try to create a table named CUSTOMERS using the following query −

SQL> CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

Let us insert some values into the above created table using the following query −

SQL> INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (2, 'Khilan', '25', 'Delhi', 1500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3, 'kaushik', '23', 'Kota', 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4, 'Chaitali', '25', 'Mumbai', 6500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (5, 'Hardik','27', 'Bhopal', 8500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (6, 'Komal', '22', 'MP', 9000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (7, 'Muffy', '24', 'Indore', 5500);

Once the table is created, let us create an index for the column named “NAME” in the CUSTOMERS table using the following query −

SQL> CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

Now, let us list all the indexes that are created on the CUSTOMERS table using the following query −

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

On executing the above query, the output is displayed as follows −

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| INDEX_NAME                       | nonclustered        | NAME             |
|                                  | located on PRIMARY  |                  |
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

Let us drop an index for the column named “NAME” in the CUSTOMERS table using the DROP INDEX command −

SQL> DROP INDEX INDEX_NAME ON CUSTOMERS;

Output

If we compile and run the above query, the result is produced as follows −

Commands completed successfully.

Verification

Let’s verify whether the index for the column named “NAME” is dropped or not using the following query −

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

As you observe the output of the above query, you can find the column named “NAME” is deleted from the list of indexes.

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

DROP INDEX with IF EXISTS

The DROP INDEX IF EXISTS statement in SQL is used to drop an index only if it exists in the table. This statement is specifically useful when you want to drop an index, but you are not sure if the index exists.

The IF EXISTS clause ensures that the statement only removes the index if it exists. If the index does not exist, it simply terminates the statement.

Syntax

Following is the syntax of the DROP INDEX IF EXISTS in SQL −

DROP INDEX IF EXISTS index_name
ON table_name;

Here,

• index_name is the name of the index that you want to drop.
• table_name is the name of the table that the index is associated with.

Example

Assume the previously created table (CUSTOMERS) and let us create an index for the “NAME” column in the table using the following query −

SQL> CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

let us list all the indexes that are created on the CUSTOMERS table using the following query −

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

On executing the above query, the output is displayed as follows −

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| INDEX_NAME                       | nonclustered        | NAME             |
|                                  | located on PRIMARY  |                  |
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

After creating the index, let us drop an index that exists in the CUSTOMERS table using the following query −

SQL> DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Output

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

Let’s verify whether the index for the “NAME” is dropped or not using the following query −

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

As you observe the output of the above query, you can find the column name “NAME” is deleted from the list of indexes.

+----------------------------------+---------------------+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| PK__CUSTOMER__3214EC27CB063BB7   | clustered, unique,  | ID               |
|                                  | primary key located |                  |
|                                  | on PRIMARY          |                  |
+----------------------------------+---------------------+------------------+

Example

Now, let us try to delete an index that doesn’t exist in the CUSTOMERS table using the following query −

SQL> DROP INDEX IF EXISTS INDEX_NAME ON CUSTOMERS;

Output

There are no indexes that exist, so the above query simply terminates the statement without giving any error.

Commands completed successfully.

Removing indexes created by PRIMARY KEY or UNIQUE

The DROP INDEX statement does not drop indexes created by PRIMARY KEY or UNIQUE constraints. To drop indexes associated with these constraints, we need to use the ALTER TABLE DROP CONSTRAINT command.

Syntax

Following is the syntax of the ALTER TABLE DROP CONSTRAINT command in SQL -−

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Here,

• table_name is the name of the table that contains the ‘PRIMARY KEY’ constraint.
• constraint_name is the name of the ‘PRIMARY KEY’ constraint that you want to drop.

Example

Assume the previously created table (CUSTOMERS) and let us first try to list all the indexes that are created on the table using the following query −

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

On executing the above query, the output is displayed as follows −

+----------------------------------+------------------- -+------------------+
| index_name                       | index_description   | index_keys       |
+----------------------------------+---------------------+------------------+
| PK__CUSTOMER__3214EC27CB063BB7   | nonclustered        | ID               |
|                                  | located on PRIMARY  |                  |
+----------------------------------+---------------------+------------------+

Here, the “PK__CUSTOMER__3214EC27CB063BB7” is the name of the PRIMARY KEY constraint that was created on the “ID” column of the CUSTOMERS table.

Now, let’s try to delete the index created by the ‘PRIMARY KEY’ constraint.

SQL> ALTER TABLE customers
DROP CONSTRAINT PK__CUSTOMER__3214EC27CB063BB7;

Output

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

Let us verify whether it is deleted or not using the following query −

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

As you observe the output of the above query, you can see that it returns an error because the list of indexes is empty.

The object 'CUSTOMERS' does not have any indexes, or you do not have permissions.