Drop an index in SQL Server with a script

Another IT and more specifically a database tutorial on how to drop an index from a table in SQL Server. An index is associated with an existing table in a database. It can be a relational or transactional database.

For example, learn here why it is important to implement maintenance plans to improve performance and management of MS SQL resources.

Drop an index in SQL Server with a script (DROP INDEX)

Firstly, to delete an unnecessary index, simply copy and paste this sample code and adjust it with the index and table names. This code will delete the index created in this article.

It is also possible to delete an index from the SSMS GUI.

This SQL Server command uses the DROP command and the INDEX keyword. The full name of the index and of course the table name follow the DROP INDEX command.

Indeed, an index has a unique name for a given table. Name the indexes with the table prefix. To distinguish them and make it easier to manage the T-SQL code.

It is possible to have the same index name on different SQL Server tables

In this SQL script, the two tables SALES and SALES_2 have the same index created with the same name.

CREATE TABLE [dbo].[SALES]
(
   [Year] TINYINT,
   [MonthName] NVARCHAR(50),
   [MonthCurrent] BIT,
   [NumberMonth] TINYINT,
   [EmployeeNumber] SMALLINT,
   [NumberOfClients] INTEGER,
   [NumberOfSales] BIGINT,
   [Amount_ET] NUMERIC(15,5),
   [Amount_IT] DECIMAL(15,5)
);

CREATE INDEX indexYearMonth
ON [dbo].[SALES] ([Year], [MonthName]);
GO

CREATE TABLE [dbo].[SALES_2]
(
   [Year] TINYINT,
   [MonthName] NVARCHAR(50),
   [MonthCurrent] BIT,
   [NumberMonth] TINYINT,
   [EmployeeNumber] SMALLINT,
   [NumberOfClients] INTEGER,
   [NumberOfSales] BIGINT,
   [Amount_ET] NUMERIC(15,5),
   [Amount_IT] DECIMAL(15,5)
);

CREATE INDEX indexYearMonth
ON [dbo].[SALES_2] ([Year], [MonthName]);
GO

Script to delete a MS SQL index from a table

Secondly, this is the same syntax as most objects deleted with Data Definition Language (DDL) queries.

DROP INDEX indexYearMonth
   ON [dbo].[SALES] ;
GO

In addition, please note that after deleting the index, all queries will use the primary key of the SQL table. This can have a huge impact on performance.