I'd like to add something to the note from jpipes about horizontal partitioning.

We recently partitioned a 65 million row table into quarterly tables of 10 million or so records each. I followed the instructions in the topic "Creating a Partitioned View" in SQL Server Books Online. These instructions were very useful, and our partitioned view works just fine. The only catch was that some types of changes to the structure of the partition tables required check constraints to be "reapplied" in order for SQL Server to recognize the view as a real partitioned view and optimize properly. Hopefully, my comment will make sense after you look at "Creating a Partitioned View".

Our new partitioned view is working great. We've realized fairly significant performance improvements for all types of queries. We're using SQL Server Standard Edition, which works fine for reading from a partitioned view. Inserting records into partition tables "through" the partitioned view requires Enterprise Edition. We get around this by writing to the partition tables directly.