Hello, one of my table (activityTable) will have big number of rows up to several hundreds of million.
To handle these data in more slimmer way, I have two different ideas in my mind.
#1. Divide the activityTable by Dept into many smaller tables which will hold serveral millions of rows such as activityTable_Dept001, activityTable_Dept002, ... and so on.
#2. Keep all data in one activityTable and whenever retrieving I use stored procedure to get only rows for certain Dept and have a new table (virtual) in the Database side, so that it looks like that we have table only for the Dept from front side.
I personally like the idea #2, but is it realistic?
Since I am a beginer, I ask anyone who read this to understand my stupid question.
Rather than partitioned views, in SQL 2005, you can use a PartitionFunction and PartitionScheme on a single table to partition it. That way you don't have the extra headache of managing N different tables each with their own indexes. SQL Server treats it as N different tables which can each be in a different filegroup (and hence different drive), but from your perspective it's just one table, with one set of indexes. Easier to manage IMO.