For auditing purposes some database system run without deleting rows from the database – they just flag the row as being deleted and ensure that queries that run on the table add a line to the WHERE clause to filter out the “deleted” rows.
However, as Daniel Turini points out on Code Project, this can hurt the database performance.
Specifically he says:
Bear in mind that having a flag leads to bad index performance. In my (rather old) SQL Server DO’s and DONT’s[^], I suggest you not to index on the “sex” or “gender” field:
First, let’s understand how indexes speed up table access. You can see indexes as a way of quickly partitioning a table based on a criteria. If you create an index with a column like “Sex”, you’ll have only two partitions: Male and Female. What optimization will you have on a table with 1,000,000 rows? Remember, mantaining an index is slow. Always design your indexes with the most sparse columns first and the least sparse columns last, e.g, Name + Province + Sex.
Creating a “deleted” flag will lead to bad performance, on several common cases. What I’d suggest is to move those rows to a “Deleted” table, and have a view using UNION ALL so you can easily access all the data for auditing purposes.
Not to mention that it’s easy to forget an “AND NOT IsDeleted” in some of your queries.
However, others recon the performance hit is negligable. Or that moving the data to a new table could damage the data. It is an interesting debate and you can read it here, from the start.
NOTE: This was rescued from the Google Cache. The original date was Thursday, 29th June 2006.
Tags: 
database delete flag audit row performance data integrity
The Blog of Colin Mackay 