Suggestion required for Large Database Optimization and performance
SQL Server DENALI will have column store indexes but this won't come until the end of this year or early next year but from what I have seen so far (very little), it will definitely help.

In SQL Server partitioning is your best friend here. And to your question abt the limitation of # of rows in a table, it is limited only by storage space.

http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx

As tables grow, adding and updating the data will take some time and you may want to look at using minimal logging if you are using SQL Server 2008.

http://sqlcat.com/whitepapers/archive/2009/02/15/the-data-loading-performance-guide-2008.aspx

And for large tables, updating indexes frequently can take significant times and have seen cases where updating the statistics will give you a quick boost instead of frequent index rebuilds.