Drop Indexes in SQL

Drop Indexes in SQL Server

Drop Indexes in SQL Server

In this post I will explain all about Drop Indexes in SQL Server end to tend along with the appropriate examples. So let’s start –

Drop Indexes in SQL Server Part – 18

We can drop any existing indexes from our current database using DROP INDEX statement –

Syntax for SQL Server 2016 and above

DROP INDEX IF EXISTS index_name

ON table_name

Syntax for SQL Server below 2016

DROP INDEX index_name

ON table_name

As per above syntax we have to specify the name of the index that we want to delete or remove from our database after DROP INDEX clause and then specify the name of the required table to which we need to remove or delete the belonging index. IF we will execute the query by removing an index which is not available on that table then SQL Server will produce an error. However we can use the IF EXISTS clause to conditionally drop the required index to avoid the raised error. I would like update one thing here that the IF EXISTS clause has been introduced in Microsoft SQL Server 2016 edition onward, please keep it in mind because in previous version it will not run.

IF we have created any indexes by PRIMARY KEY or UNIQUE constraints then DROP INDEX statement doesn’t remove. In this case we have to use as ALTER TABLE then DROP CONSTRAINT statement first.

IF we want to delete or remove multiple indexes from one or more table at the same time then we have to specify a comma separated list of index names with the corresponding table names after the DROP INDEX clause as shown in below syntax –

Syntax for SQL Server 2016 and above

DROP INDEX IF EXISTS

index_nameA ON table_nameA,

index_nameB ON table_nameB,

index_nameC ON table_nameC

Syntax for SQL Server below 2016

DROP INDEX

index_nameA ON table_nameA,

index_nameB ON table_nameB,

index_nameC ON table_nameC

Examples – We will use two different tables like Price and Stud from our training database to show the live example here. We can see that there is one index(ix_Stud_ID) available in Stud table and two indexes (ix_price_price, PK__Price__3FB50F942CC78022) available in Price table in below snapshot –

Now we will remove one index – PK__Price__3FB50F942CC78022 from price table using DROP INDEX statement –

Synatx:

DROP INDEX PK__Price__3FB50F942CC78022 ON dbo.Price

SQL Server thrown an error like mentioned below –

Message:

Msg 3723, Level 16, State 4, Line 3
An explicit DROP INDEX is not allowed on index ‘dbo.Price.PK__Price__3FB50F942CC78022’. It is being used for PRIMARY KEY constraint enforcement.

As we stated above that we can not delete or remove any index if that was created by Primary key or UNIQUE, and here we can see that this Index has been created by PRIMARY KEY only then we have to drop this constraint key using below query –

Syntax:

ALTER TABLE Price
DROP CONSTRAINT PK__Price__3FB50F942CC78022

Result:

Command(s) completed successfully.

Now we can refresh Indexes folder and see the index has DROPPED –

Now we will remove multiple indexes like – ix_price_price, ix_priceOne_price, ix_priceTwo_price from price table and ix_Stud_ID index from Stud table using DROP INDEX statement –

Syntax:

DROP INDEX
ix_price_price ON dbo.Price,
ix_priceOne_price ON dbo.Price,
ix_priceTwo_price ON dbo.Price,
ix_Stud_ID ON dbo.Stud

Message:

Command(s) completed successfully.

Now we can see easily that all the indexes has been removed.

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply