How to store 'n' days of web server logs in Sql Server?
You should look in to partitioning.

http://technet.microsoft.com/en-us/library/dd578580%28SQL.100%29.aspx

The cool thing about partitioning is that you have just one table name (as opposed to the multiple table approach) so your insert statements remain static.  It works with every application - it's completely transparent to queries.  You don't have to worry about what happens if you end up with different indexes or statistics on each of the tables, either.

You create a partition function that decides how to break up the table into multiple tables behind the scene.  The function can only take one input parameter/field, and in your case, it would be a date field.  The function can break up the table by date, week, month, or year - in your case, you'd want date, 24-hour period.

Then build a SQL Server Agent job that uses T-SQL to swap out the last partition every day.  The delete becomes a metadata operation, and it's blazing fast.  Swap the partition, then drop the old one out.