SQLServerPedia

Larry wrote in and asked:

“Is there a TSQL command sequence that will allow SQL DELETE statements to be issued without triggering any log growth? Assume I can change the recovery model.”

Well, sort of – if you want to delete records without triggering as much log growth, there’s a different command to use instead:

TRUNCATE TABLE dbo.MyTableName

The truncate table command will empty out your table faster than you can say, “Whoops!”  Of course, that’s dangerous, but it comes in handy for things like data warehouse nightly ETL processes where you need to empty out huge work tables as quickly as possible.

The bad news: truncate table doesn’t accept a where clause.  It simply deletes all of the records in the table without logging each individual delete operation.  It’s quick, but it’s dangerous.