Best approach to optimize a record history database

I have a database that keeps record history. For each update to a record, the system will "deactivate" the previous record (along with all it's children), by setting the "Status" column to "0".

Now it's not a problem yet...but eventually this system is going to have a lot of records, and history is more important than speed right now. But the more records inserted, the slower searches 开发者_JAVA技巧become.

What is the best approach to archive the records? I've had suggestions to create a cloned archive database to hold the data. I've also had the idea to storing all previous records into a xml file, that can be read / loaded later if we need to dig up archived records.

You could create a separate partition containing only the active record if your DBMS supports it. You can also add an index to Status so that the select ... from tbl where status=1 isn't incredibly slow.

http://msdn.microsoft.com/en-us/library/ms187802.aspx