Learning Objective

The objective of this SQL Server tutorial is to teach you how to remove an index from a table or view.

Introduction to SQL Server DROP INDEX

What is an INDEX?

An index is a list of items ordered alphabetically or on some other ordering criteria so that the items and the information about the items can be accessed easily and quickly. A database index also serves the same purpose as a normal index and allows us to access and retrieve a row of data in less time with less effort using lesser computing resources. There are 3 main types of indexes in SQL Server. They are Clustered Index, Non-Clustered Index and Unique Index.


An index can exist on both tables and views. The command to remove an index is DROP. It can be used to drop one or more or all indexes associated with a table or view at the same time in a single command. It is also possible to drop multiple indexes each associated with a different table or view if the tables or views belong to the same database at the same time in a single command. We will go through all the scenarios and their examples.

SQL Server DROP Index Syntax

The basic syntax of the command is twofold. One syntax is the following.

[table_name1|view_name1]. index_name1,
[table_name2|view_name2]. index_name2, …………;

In this syntax,

  • IF EXISTS – this is an optional. If added and any index specified in the command does not exist it will not throw an error. If skipped it will throw an object not found error. For the most part it is unnecessary since we will not be deleting a non-existent index and even if we do and get an error it does no harm.
  • table_name|view_name – it can be either one of the two since an index will be either associated with a table or a view and not both at the same time.

The other syntax is the below where the index name is specified with the table or view using the ON clause.

index_name1 ON [table_name1|view_name1],
index_name2 ON [table_name2|view_name2], …………;

SQL Server DROP INDEX Examples

Let us consider that there are 2 tables in a database – students and students _other_info. The first table contains basic information including the student name, roll number and stream of study and the second table contains some supplementary information about hostel and scholarship amount. The 2 tables are represented below.

11Amy                                              Williams                                         Arts               
13Laura                                            Wells                                            Science            
23Rizwan                                           Ahmed                                            Commerce           
25Nick                                             Jones                                            Science            
29Michael                                          Bull                                             Science            
30Val                                              Kostner                                          Science            
33Wayne                                            John                                             Commerce           
35Julie                                            Summers                                          Arts               
Table: Students
11Teresa Hostel                300
13Teresa Hostel                500
29Xavier Hostel                500
33Xavier Hostel                400
35Johns Hostel                 300
Table: students_other_info

Both the tables have indexes associated with them. We can see the indexes in the screenshot taken from SQL Server Management Studio (SSMS) below. The first table has a default clustered index on the primary key rollno and an additional non-clustered index on the stream column. The second table also has a default clustered index on the primary key rollno.

SQL Server drop index

The same can also be checked using the SP_HELPINDEX system stored procedure.

ix_stream_indexnonclustered located on PRIMARYstream
PK_studentsclustered, unique, primary key located on PRIMARYrollno
EXEC SP_HELPINDEX students_other_info;
PK__students__FABA8B5BF5432D6Dclustered, unique, primary key located on PRIMARYrollno

1) Dropping a single Index

In this first example we will drop a single index. The following command does the same and drops the additional non-clustered index ix_stream_index from the first table students.

DROP INDEX students. ix_stream_index;

After dropping we can check and verify using SP_HELPINDEX or the SSMS GUI. It shows only the default clustered index remaining.

PK_studentsclustered, unique, primary key located on PRIMARYrollno
sql server drop index - dropping a single index

2) Dropping multiple Table Indexes

In this example we will delete the remaining 2 indexes – one from each table. That is the default clustered index of both the students and students_other_info table with a single query in one go. However before doing that we will have to disable the rollno column as the primary key from both the tables because SQL Server does not allow explicit drop operation on the default clustered index built on the primary key. After doing so we can drop the default clustered index from both the tables. The below query does the same. Note that this time I have used the second syntax along with the optional IF EXISTS clause.

PK_students ON students,
PK__students__FABA8B5BF5432D6D ON students_other_info;

After dropping we can check and verify using SP_HELPINDEX or the SSMS GUI. It will show that there are no indexes for either table as below.


The object 'students' does not have any indexes, or you do not have permissions.
Completion time: 2020-09-28T02:16:32.9792022+05:30
EXEC SP_HELPINDEX students_other_info;

The object 'students_other_info' does not have any indexes, or you do not have permissions.
Completion time: 2020-09-28T02:18:49.3398964+05:30

3) Dropping Indexes from Views

In this last example we will create a view and two indexes on it and then drop them both in one go with a single command. The below command creates a view called students_view selecting the first 3 columns of the students table.

CREATE VIEW students_view
AS SELECT rollno, firstname, lastname
FROM dbo.students;

The following 2 commands creates 2 indexes. One unique clustered index and another non-clustered index.

students_view (rollno);

We can check the indexes with SP_HELPINDEX and SSMS GUI. Both will show the indexes.

EXEC SP_HELPINDEX students_view;
ix_nc_students_viewnonclustered located on PRIMARYlastname
ix_students_viewclustered, unique located on PRIMARYrollno

Finally, we will delete both the above indexes by executing the following command. Note that I have omitted the unnecessary IF EXISTS.

ix_nc_students_view ON students_view,
ix_students_view ON students_view;

After dropping the indexes, we can check to verify with SP_HELPINDEX and SSMS GUI. Both will show the absence of any index on the view as below.

EXEC SP_HELPINDEX students_view;

The object 'students_view' does not have any indexes, or you do not have permissions.
Completion time: 2020-09-28T02:40:29.4809658+05:30