How to Efficiently Delete the Top 1000 Rows from an SQL Server 2008 Table?

Deleting the Top 1000 Rows from an SQL Server 2008 Table

In SQL Server 2008, you can efficiently purge the top 1000 records from a table using a combination of a common table expression (CTE) and a subsequent delete statement. Here's how it works:

Original Problematic Query:

The code you initially attempted deletes all rows in the table because it consists of two separate statements: a delete followed by a select. The select statement does not specify an ordering criterion, leading to the deletion of all rows.

Corrected Code Using CTE:

To address this, we utilize a CTE named 'CTE' to select the top 1000 rows in ascending order based on the 'a1' column. The subsequent delete statement then targets the CTE, ensuring that only those specific records are removed:

;WITH CTE AS
(
  SELECT TOP 1000 *
  FROM [mytab]
  ORDER BY a1
)
DELETE FROM CTE

By employing this method, you can efficiently delete the desired number of rows from the table without affecting the remaining records.

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