SQL Server Performance

Delete

Discussion in 'General Developer Questions' started by Reddy, Sep 30, 2005.

  1. Reddy New Member

    Hi
    How can I delete last 10 records from a table. so that after getting rid of last 10 records, when again there is an insertion to that table it shud cathup the IDENTITY values.

    Thanks!
    "He laughs best who laughs last"

  2. Adriaan New Member

    This is the query that removes the records with the top 10 highest values on the identity column.

    DELETE
    FROM table
    WHERE identity_column
    IN (SELECT TOP 10 identity_column
    FROM table ORDER BY identity_column DESC)

    If you need to delete the top 10 for a different field, or the lowest values, you make the change in the ORDER BY of the subquery only.

    You must use an ORDER BY clause, otherwise it is unpredictable which records get deleted. Never assume that SQL selects/updates/deletes rows in the order of the primary key!

    You can also use the following, but it is less 'straight'.

    SET ROWCOUNT 10
    DELETE FROM table ORDER BY identity_column DESC
    SET ROWCOUNT 0

    After the delete, you run DBCC CHECKIDENT 'table', RESEED to reset the identity column.
  3. Reddy New Member

    That looks gud, thanks Adriaan

    Thanks!
    "He laughs best who laughs last"

Share This Page