Deleting top 'n' rows from table
Posted on 2003-02-26
I have to maintain the size of tables in my database.
The records are inserted at realtime at a frequency of say 2 records per second.
So to maintain the size of tables i deleted the oldest records(FIFO)from the table by an insert trigger.
This is causing performance problems and I switch over to sql server agent's job shedule, which executes a procedure once in a day and checks if the rowcount of the table > 200000. If it is so then it has to delete the oldest records to maintain the table size @ 200000.
To do this i wrote a procedure like this.
declare @excessrows integer
set @excessrows = 200000 - (select count(*) from table)
delete table from (select top @excessrows * from table ) as t1 where table.date = t1.date
This is not working as TOP clause does not takes variables
and if we give TOP 1000 it works.
I dont want to use Set RowCount, since other parallel queries will be affected..
So how to maintain the size of table in SQLServer?