SQL Server DROP INDEX

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.

Operation

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.

DROP INDEX
IF EXISTS
[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.

DROP INDEX
IF EXISTS
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.

rollno	firstname	lastname	stream
11	Amy	Williams	Arts
13	Laura	Wells	Science
23	Rizwan	Ahmed	Commerce
25	Nick	Jones	Science
29	Michael	Bull	Science
30	Val	Kostner	Science
33	Wayne	John	Commerce
35	Julie	Summers	Arts

rollno	hostel_name	scholarship_amount
11	Teresa Hostel	300
13	Teresa Hostel	500
29	Xavier Hostel	500
33	Xavier Hostel	400
35	Johns Hostel	300

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.

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

index_name	index_description	index_keys
ix_stream_index	nonclustered located on PRIMARY	stream
PK_students	clustered, unique, primary key located on PRIMARY	rollno

EXEC SP_HELPINDEX students_other_info;

index_name	index_description	index_keys
PK__students__FABA8B5BF5432D6D	clustered, unique, primary key located on PRIMARY	rollno

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.

index_name	index_description	index_keys
PK_students	clustered, unique, primary key located on PRIMARY	rollno

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.

DROP INDEX
IF EXISTS
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.

EXEC SP_HELPINDEX students;

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
WITH SCHEMABINDING
AS SELECT rollno, firstname, lastname
FROM dbo.students;

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

CREATE UNIQUE CLUSTERED INDEX
ix_students_view
ON
students_view (rollno);

CREATE NONCLUSTERED INDEX
ix_nc_students_view
ON
students_view(lastname);

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

EXEC SP_HELPINDEX students_view;

index_name	index_description	index_keys
ix_nc_students_view	nonclustered located on PRIMARY	lastname
ix_students_view	clustered, unique located on PRIMARY	rollno

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

DROP INDEX
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