How to delete all but the latest 20,000 records in MS SQL 2005?

Question

Every night I need to trim back a table to only contain the latest 20,000 records. I could use a subquery:

delete from table WHERE id NOT IN (select TOP 20000 ID from table ORDER BY date_added DESC)

But that seems inefficient, especially if we later decide to keep 50,000 records. I'm using SQL 2005, and thought I could use ROW_NUMBER() OVER somehow to do it? Order them and delete all that have a ROW_NUMBER greater than 20,000? But I couldn't get it to work. Is the subquery my best bet or is there a better way?

Answer 1

If it just seems inefficient, I would make sure it is inefficient before I start barking up the wrong tree.

Measure the time, cpu usage, disk I/O, etc. to see how well it performs. I think you'll find it performs better than you think.

Answer 2

Of course, your mileage will vary -- This will depend on how many real records you are scraping off the bottom of this table, but here's an alternative.

Side Note: Since you have a "Date_Added" field, would it be worth considering to simply keep the datetime of the last run and use that in your where clause to filter the records to be removed? Now, instead of 20,000 records, allow X number of days in the log ... Just a thought...

---

-- Get the records we want to KEEP into a temp.
-- You can classify the keepers however you wish.

select top 20000 * into #myTempTable from MyTable ORDER BY DateAdded DESC

-- Using truncate doesn't trash our log file and uses fewer sys resources...

truncate table MyTable 

-- Bring our 'kept' records back into the fold ...
-- This assumes that you are NOT using an identity column -- if you are, you should
-- specify the field names instead of using the '*' and do something like
-- SET IDENTITY_INSERT MyTable ON
-- insert into MyTable select field1,field2,field3 from #myTempTable
-- (I think that's right)

insert into MyTable select * from #myTempTable

-- be a good citizen.

drop table #myTempTable

---

Hope it helps --

Answer 3

DECLARE @limit INT
SELECT @limit = min(id) FROM
   (SELECT TOP 20000 id FROM your_table ORDER BY id DESC)x
DELETE FROM your_table where id < @limit

The point was to avoid the nested query, which I may or may not be optimized (sorry not sql guru.)

Answer 4

insert 20000 into temp table then delete all records from main table then again insert 20000 record from temp table to main table..,