I don't like to delete records in our production database, even though we will never need them again. Does this affect performance?

Question

I don't like to delete records in our production database, even though we will never need them again. Because of this, I have included an extra column in every table called the "ACTIVE_FLAG" that is set to one character in length that contains either an "a" for active records or a "d" for deleted records. How does this affect performance?

Answer

I have seen some database designs like yours, but generally speaking, it is because there is a fairly good chance that the records will be needed again, and flagging the record is an easy way to toggle access to them or not. But if you don't think the records will ever be needed again, I wouldn't recommend your method because it will negatively affect your server's performance.

Even though you may not need to access the inactive records and are marked accordingly, they still take up disk space. This is space for the record itself, along with any space taken by indexes you have on your tables. Unnecessary data in a table forces SQL Server to perform more I/O than necessary when reading data pages, which in turn slows down performance. Ideally, the only data you want in your database is data that someone needs. If nobody is using data in a database, it should be removed.

If you think there is a possibility that you might need the records again, another option is to archive the records in another database. For example, you could flag a record, like you do now, to render it inactive. Then periodically, such as one a month, or a quarter, or a year, move those records from the production database to another database on the same or different server. This way, the records are still available, but they don't take up unnecessary space in the production database, hindering performance.

Depending on the proportion of inactive records to active records, you may find that after archiving inactive records, that your database's performance increases significantly.

Reader's Feedback

We currently utilize triggers to archive deleted (and updated) records to a separate database in order to track all changes for important tables.  Seems to work well since we don't have too many users with access greater than read.  Also, users who do have access levels that allow updates and deletes are limited in the number of records they can access at one time.  Those users are also limited to certain tables based on access levels.  If a business user decides they want to do more than normally allowed they make a request to the administrator, which further serves the tracking process. --CH