Quick Tip: Deleting all Data from a Table... FAST!!

There are times when you need to “blank out” a table. That is to say, remove all data from the table, but leave the table to be repopulated.

Many people opt for:

DELETE FROM MYTABLE

This certainly works. However, if you have a lot of records (and especially if there are a lot of indexes as well) this approach can be time consuming.

The reason is simple. SQL Server tracks all changes made in a database so they can be rolled back (Using Begin Tran/Rollback/Commit commands). Also, SQL Server is tracking these commands for disaster recovery. If the server goes down later, these changes are stored in a way that SQL Server can reconstruct the database when it boots back up and recovers. So, for every row you delete, SQL Server is writing to a log about that delete action. As we all know, writing data takes some time.

A quicker option is to use the following:

TRUNCATE MYTABLE

This is basically instantaneous. SQL Server merely logs the fact that a truncate happened. So, if you delete two million records, only one log entry is made. SQL Server then simply changes some internal pointers, and *poof* it’s done. BE VERY AWARE THAT YOU CANNOT ROLLBACK A TRUNCATE STATEMENT. That is the price you pay for speed!!

One final note, if you have an Identity field in your table (otherwise known as an auto-increment field from those of you coming from an Access background) then Truncate will reset your starting number to 1 (or whatever you defined as your starting number). This can cause some problems in some apps that depend on the field continuing to increment. If you need the field to continue to increment, there is a technique for that. If your interested in that technique, just drop us a comment and we’ll post it for you.