How to delete the more than 10 records from top table, using Sql Server 2008?

Question

If the number was more than 10 records, Old records clean. that's mean,If the 15 records stored in the table, 5 first records to be erased.

Example:

"DELETE FROM Table WHERE ID NOT IN (SELECT ??? 10 ID FROM Table)"

Answer 1

In SQL Server, you can use row_number() to enumerate the values and then delete the oldest ones using where:

with todelete as (
      select t.*, row_number() over (order by id desc) as seqnum
      from t
      )
delete from todelete
    where seqnum > 10;

Your approach also works. You can do:

delete from t
    where t.id not in (select top 10 t2.id from t t2 order by t2.id desc);

Note: This uses not in, so it assumes that id is never NULL. That seems reasonable in this situation.