Suggestion for Large SQL Server Database Design We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very few UPDATEs or DELETEs on the data. Its a data archival system where we insert historic records on a daily basis. We will generate different sort of reports on this historic record on user request so we've some concerns and require technical input and advice.


What is the best way to manage this kind of archival tables and database?
Here is my opinion:


If you are having very few updates/deletes you can increase the pagefill factor to 95%. This will save on space and reads. Do some testing though. 
Partition the table based on a broad category like year. 
Put these partitions on different filegroups.