robblackin (9/15/2009)
I just had to deal with this. However, I need to have an hourly sliding window where I delete records older than 3 hours for working with .net viewstate.I had the transaction log in simple mode and was deleting in small batches. However, the deletes were not keeping up with the inserts. Since it is viewstate the records are very big.
What I did was set up a partitioned view and just truncate the table in the view thereby deleting 200,000 records in less than a second and not affect the inserts.
IMHO, truncating partitions or tables in a partitioned view is much cleaner and works faster than this method.
If you are in a position to use paritioned tables in SQL Server 2005/2008, then yes I agree. Unfortunately, this may not be possibile for several reasons. One, you may be using SQL Server 2005/2008 Standard Edition where partitioned tables aren't available. Or, your data may not be setup using partitioned views (and therefore separate tables). If that is the case, then your option isn't available. I am just providing an option. As with all things in the SQL Server world, it depends. This is just one option available to people to use when in this position.