You mentioned doing data mining so I will assume that you actually DO need all the data from the day's join query so you can run it through some mining...

>> That is correct as long you give the access to the db through application but I have seen the cases sometimes clients want to generate custom reports which requires...

As long as you include a ClientID in your action queries, it would take a real blunder to delete the wrong data I think.

Clearly you would not build an application that required new tables for each client.  The original poster implied (to my belief anyway) that they were redesigning an existing application.  The...

I currently manage 10K+ databases spread over two servers.  One of them is a reporting/warm standby server that keeps fairly recent data on it from another other server.  It is was...

Try setting your maximum memory in sql server to 3GB.

Partitioning of some sort is definitely the way to go here for best performance.  Too bad you aren't using SQL2005 - much better support for that there.  But for the...