SQL SERVER – Delete Statement and Index Usage

Recently I had a very interesting conversation with my client about Comprehensive Database Performance Health Check. The client wanted to know if the index help with the DELETE command as well. The answer is yes, Indexes do help with the DELETE statement.

Let us see a simple example of the SELECT command and its execution plan. Here you will be able to see the query using the index in the execution plan.

Now let us convert the same SELECT statement to the DELETE statement. Once you execute it, check the execution plan as well. Here are a few ways how you can enable the execution plan.

You will see in the execution plan there is a use of indexes. I guess that’s it today. A very short blog post.

Here are a few associated blog posts which you may find interesting.

• Activity Monitor and Running Query – SQL in Sixty Seconds #115
• Email an Execution Plan – SQL in Sixty Seconds #114
• Execution Plan – Estimated vs Actual – SQL in Sixty Seconds #113
• What Happens If the Clustered Index is Disabled? – Interview Question of the Week #291

Please do not forget to subscribe to my youtube channel. Let me know if you have any other SSMS tip which you think help the larger community.

Reference: Pinal Dave (https://blog.sqlauthority.com)