How to Drop Index in SQL Server

Sometimes you may need to delete index in SQL Server database, if it doesn’t perform as required. You can easily do this using DROP INDEX statement in SQL Server. In this article, we will look at how to drop index in SQL server using DROP INDEX statement.

How to Drop Index in SQL Server

We will drop index in SQL Server using DROP INDEX statement.

Here is the syntax of DROP INDEX statement.

drop index [if exists] table_name.index_name;

You can also write DROP INDEX statement another way as follows.

drop index [if exists] index_name
on table_name;

In the above SQL statements, you need to specify the table name and index name.

You can optionally specify if exists keyword to delete an index only if it exists.

For example, if you want to drop index order_id for table orders(id, order_date, amount), then here is the SQL query for it.

drop index orders.order_id;

Also read : How to Create Index in SQL Server

Drop Multiple index in SQL Server

If you want to drop multiple indexes from one or more tables at the same time, use DROP INDEX statement as shown below.

DROP INDEX [IF EXISTS] 
   index_name1 ON table_name1, 
   index_name2 ON table_name2, 
   ...;

In the above statement, you need to specify index names and table names in a comma separated format.

Also read : How to Copy data from one table to another in SQL Server

Drop Primary Key Index in SQL Server

If you want to drop primary key index then you need to use ALTER TABLE statement. Here is the syntax

ALTER TABLE table_name
DROP CONTRAINT pk_table_name;

In the above SQL query, you need to specify table name and primary key constraint name. Here is an example,

alter table orders 
drop constraint pk_orders;

Also read : How to Alter column from NULL to NOT NULL

As you can see, it is really easy to drop index in SQL Server.