Delete oldest records after maximum row count is reached in a SQL Server table?
Posted on 2012-03-20
(Need an answer for either SQL Server 2005 or 2008.)
I have a SQL Server table whose size I want to limit. I want to be able to specify that only, say, 50,000 records are allowed. I do not want to prevent the addition of new records. Rather, I want to drop the oldest records as new records are added so that I retain only the most recent 50,000.
Each record has an auto-incremented field with an increment of 1. It is possible I could be inserting hundreds of records at once, several times a day.
I am guessing this could be handled by some type of insert trigger. I would also consider some kind of scheduled process that would run each day (or week) and simply delete all the unwanted records at that time to keep reducing the record count to 50,000.