Sql-server – Suggestion for Large SQL Server Database Design

data-warehousedatabase-designdatabase-recommendationsql server

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?

Best Answer

Here is my opinion:

  1. 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.
  2. Partition the table based on a broad category like year.
  3. Put these partitions on different filegroups.
Related Question