SQL Query to delete oldest rows over a certain row count?

Question

I have a table that contains log entries for a program I'm writing. I'm looking for ideas on an SQL query (I'm using SQL Server Express 2005) that will keep the newest X number of records, and delete the rest. I have a datetime column that is a timestamp for the log entry.

I figure something like the following would work, but I'm not sure of the performance with the IN clause for larger numbers of records. Performance isn't critical, but I might as well do the best I can the first time.

DELETE FROM MyTable WHERE PrimaryKey NOT IN
(SELECT TOP 10,000 PrimaryKey FROM MyTable ORDER BY TimeStamp DESC)

I should mention that this query will run 3-4 times a day (as part of another process), so the number of records that will be deleted with each query will be small in comparison to the number of records that will be kept.

Answer 1

Try this:

DECLARE @X int
SELECT @X=COUNT(*) FROM MyTable 
SET @X=@X-10000

DELETE MyTable 
WHERE PrimaryKey IN (SELECT TOP(@x) PrimaryKey 
                     FROM MyTable 
                     ORDER BY TimeStamp ASC
                    ) 

kind of depends on if you are deleting fewer than 10,000 rows, if so this might run faster, as it identifies the rows to delete, not the rows to keep.