SQL SERVER – Delete Statement and Index Usage

An index can help a DELETE statement locate rows to remove. A customer asked whether indexes benefit modifications alongside SELECT queries.

CREATE TABLE #DeleteExample (ID int NOT NULL PRIMARY KEY, Value int NOT NULL);
INSERT #DeleteExample VALUES (1,10),(2,20),(3,30);
SELECT ID, Value FROM #DeleteExample WHERE ID = 2;
DELETE #DeleteExample OUTPUT deleted.ID, deleted.Value WHERE ID = 2;
SELECT ID, Value FROM #DeleteExample ORDER BY ID;
DROP TABLE #DeleteExample;

The actual SELECT uses a clustered index seek. The actual DELETE uses a clustered index delete with a seek predicate. Both statements target ID 2; the result and plan counters show one row.

The temporary-table companion removes its own demonstration row. Enable Include Actual Execution Plan before execution. Compare the access path for ID 2 with the modification operators. Historical screenshots illustrate their original table rather than this new batch.

DELETE also maintains affected indexes and constraints and can execute triggers. A cheaper search can still add index-maintenance cost. Large deletions can favor scans. I assess the complete statement under a representative workload.

Start with an estimated plan and a controlled test copy for production work. Don’t replace SELECT with DELETE on live data to inspect index usage. The related execution-plan videos offer additional examples.

Related reading

• Comprehensive Database Performance Health Check
• 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
• Pinal Dave on YouTube

A cheaper row search is not the whole DELETE cost, it is one part of finding and modifying affected data.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.