Indexing and Delete Operation in SQL

Hello and Good Evening Friends!

Yesterday I got one scenario through my colleague. It was actually regarding SQL DB,  he was trying to delete around (millions of records) but it was too long (more than 2 hours and executing) to execute the query.

I went and saw his table thoroughly and found he was created many more indexes on table (including Clustered and Non-Clustered). I told him Indexing helps us to fast performance to get the result (as without indexing it scans the entire table and fetch the expected result) but slow the performance when we apply the INSERT, UPDATE and DELETE statements over the table(s).

To resolve such kind of issues, you need to do the following steps:

1.       Drop the Indexes(on tables) first before Executing Delete Query                                                         DROP INDEX IX_IndexName  ON dbo.TableName;

2.       Fire the DELETE FROM TABLE Query

3.       CREATE  the Indexes(on tables) 

       CREATE INDEX IX_IndexName  ON dbo.TableName(Column_Name);