How to Efficiently Delete All But the Top N Rows in an SQL Table?

Efficiently Deleting Rows in an SQL Table, Retaining Only the Top N

Consider the scenario where you need to purge a database table of all but the top n rows, based on a certain criterion. This task can be achieved in SQL using various approaches, and here we explore an effective solution:

Option 1: Using the DELETE and SELECT TOP Statements

This approach leverages the DELETE statement to remove rows and the SELECT TOP statement to identify the top n rows. The syntax is as follows:

DELETE FROM Table
WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)

However, as Chris rightly points out, this method can incur performance penalties due to the execution of the SELECT TOP query for each row to be deleted.

Alternative Option: Utilizing Temporary Tables for Optimization

To mitigate the performance issue, consider the following optimized approach:

SELECT TOP 10 ID INTO #Top10
DELETE FROM Table
WHERE ID NOT IN (SELECT ID FROM #Top10)
DROP TABLE #Top10

This method involves creating a temporary table (#Top10) to store the top n IDs. The DELETE statement then uses this temporary table to efficiently remove the remaining rows. Finally, the temporary table is dropped to free up system resources.

This alternative approach significantly enhances performance by eliminating the need to execute the SELECT TOP query for each row deletion, resulting in faster execution times for large tables.

The above is the detailed content of How to Efficiently Delete All But the Top N Rows in an SQL Table?. For more information, please follow other related articles on the PHP Chinese website!