<p>I'm leading a project where we'll be recording metrics data. I'd like to retain the data for years. However, I'd also like to keep the primary table from becoming bloated with data that, while necessary for long term trending, isn't required for short term reporting.</p>

<p>What is the best strategy for handling this situation? Simply archive the old data to another table? Or "roll it up" via some consolidation of the data itself (and then store it off to a different table)? Or something else entirely?</p>

<p><i>Additional info: we are using SQL Server 2005.</i></p>

Either of those options are excellent, but it really depends on the problem domain. For things like cash balances or statistical data, I think that rolling up records and consolidating them is the best way, you can then move the rolled up records into a parallel archive table, keying them in such a way that you can "unroll" if necessary. This keeps your primary data table clean and quick, but allows you to retain the extra data for auditing or whatever. The key question is, how do you implement the "roll-up" process. Either automatically, via a trigger or server side process, or by user intervention at the application level?