How to delete multiple rows in SQL where id = (x to y)

sql

delete-operations

sql-performance

sql-best-practices

byNikita Barsukov·Sep 10, 2024

For a swift and successive range of record deletions, employ:

DELETE FROM table_name WHERE id BETWEEN x AND y;

Make sure table_name corresponds to your target table, and x and y characterize the comprehensive ID range you wish to strike out.

For particular sets of rows or non-consecutive ranges, the IN operator paired with a list of selected IDs should suffice:

DELETE FROM table_name WHERE id IN (x, z, ...);

How to maximize deletion efficacy

Embracing range deletion

The BETWEEN clause is your knight in shining armor for range deletions. When not relevant due to non-linear ID order, employ inequalities, stating id >= x AND id <= y, to deliver a similar outcome.

Getting the most out of IN operator

The IN operator is a lifesaver when deleting various nonsequential IDs. It accepts a comma-separated list or a query result—ensure your subqueries output a single ID column.

Ensuring atomic batch deletions

For maintaining atomicity, enclose delete operations within transactions, ensuring everything executes successfully or not at all.

Hitting the gas pedal on deletion performance

To supercharge the deletion process:

Skip unnecessary subqueries with IN.
One DELETE command could be quicker than several ones.
If used wisely, setting the isolation level to READ UNCOMMITTED dodges locks.
Use SET NOCOUNT ON to suppress row count messages, thus speeding up operations.

Deleting specific rows without a direct range

For cases where ID ranges aren't contiguous, the IN operator does a fine job:

-- let's clean up unused IDs, they've been sitting around like gym memberships
DELETE FROM table_name WHERE id IN (3, 5, 9, 12);

Imagine selectively eliminating specific apartments, not the whole block—each id mentioned is a targeted strike.

Post-deletion checks for peace of mind

After executing a DELETE command:

@@ROWCOUNT verifies the number of affected rows.
Post-deletion sanity checks help maintain data integrity.
A BEGIN TRAN and ROLLBACK pairing can avert unintended data catastrophe.

Navigating advanced deletion scenarios

Deletions with ripple effects

When deleting, consider the potential triggers or cascades that might lead to additional deletions.

Importing rains of IDs with XML and stored procedures

Passing an XML list of IDs could aid in complex scenarios. However, use it sparingly—only when other methods tap out.

Sorting before deleting is overkill

Pre-sorting, albeit tempting, is often counterproductive and can impair performance. SQL Server devises the most efficient deletion route, so cherry-picking is unnecessary.