How to delete the top 1000 rows from a table using Sql Server 2008?

The code you tried is in fact two statements. A DELETE followed by a SELECT.

You don't define TOP as ordered by what.

For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way.

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

sql sql-server sql-delete

May be better for sql2005+ to use:

DELETE TOP (1000)FROM [MyTab]WHERE YourConditions

For Sql2000:

DELETE FROM [MyTab]WHERE YourIdField IN (  SELECT TOP 1000     YourIdField   FROM [MyTab]  WHERE YourConditions)

BUT

If you want to delete specific subset of rows instead of arbitrary subset, you should explicitly specify order to subquery:

DELETE FROM [MyTab]WHERE YourIdField IN (  SELECT TOP 1000     YourIdField   FROM [MyTab]  WHERE YourConditions  ORDER BY ExplicitSortOrder)

Thanks tp @gbn for mentioning and demanding the more clear and exact answer.

sql sql-server sql-delete

As defined in the link below, you can delete in a straight forward manner

USE AdventureWorks2008R2;GODELETE TOP (20) FROM Purchasing.PurchaseOrderDetailWHERE DueDate < '20020701';GO

CodeHunter