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

I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but I instead of just deleting the top 1000 rows it deleted all the rows in the table.

Here is the code:

  • Update using Self Join Sql Server
  • Update every field depending on the same and other fields
  • SQL Server: convert 2 TSQL query results to XML
  • SQL Customized search with special characters
  • SQL Server Latches and their indication of performance issues
  • in a Group if records are present more than 1 with blank. delete from output
  • delete from [mytab] 
    select top 1000 
    a1,a2,a3
    from [mytab]
    

  • Selecting “latest” row (up to a date) from a table (Sql Server 2008)
  • Database Design for Attendance Management
  • How to merge the results of two divergent xpaths for Microsoft SQL Server
  • What does sp_reset_connection do?
  • What is the difference between “Is Not Null” and “Not Is Null”
  • Connect to SQL Server 2012 Database with C# (Visual Studio 2012)
  • 6 Solutions collect form web for “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
    

    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.

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

    USE AdventureWorks2008R2;
    GO
    DELETE TOP (20) 
    FROM Purchasing.PurchaseOrderDetail
    WHERE DueDate < '20020701';
    GO
    

    http://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx

    delete from [mytab]
    where [mytab].primarykeyid in
    (
    select top 1000 primarykeyid
    from [mytab]
    )
    

    It is fast. Try it:

    DELETE FROM YourTABLE
    FROM (SELECT TOP XX PK FROM YourTABLE) tbl
    WHERE YourTABLE.PK = tbl.PK
    

    Replace YourTABLE by table name,
    XX by a number, for example 1000,
    pk is the name of the primary key field of your table.

    SET ROWCOUNT 1000;
    
    DELETE FROM [MyTable] WHERE .....
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.