History/audit data is best kept in separate tables (and sometimes if
required can be even separate database).

A few cases may require the history data to be kept in the same table. For
example, in bill of material for parts you can keep old versions of the part
in the table. But history there is managed via a pair of start/end effective
dates which define the latest active part, and prior versions. This allows
to query the part to point of time and see how it was at particular date.

Also, using active/inactive flag only is not meaningful. It doesn't help to
see when this version was active and when it became inactive. Keeping a
period of active/inactive dates is normally a lot better and allows to
answer very easy queries like "What was the active version of this item by
that date?".

Partitioning could be another approach, but not sure applicable in your
case.

HTH,

Plamen Ratchev
http://www.SQLStudio.com