Just a day ago, I received following question:
“I have large table more than 1M rows. I want to delete every row in my table. Everytime I ran DELETE statement, it times out and does not do it job. The data in table is useless and I do not need it ever. Your suggestion please.”
The reason I decided to write article about this question because I receive similar questions very often. I think many readers will find answer to this question useful. My answer to his question is here with:
“If DELETE is timing out use TRUNCATE instead. It will be faster and will not be logged in log file.”
However, I recommended few additional points to keep in mind while using TRUNCATE.
Command TRUNCATE will reset identity to initial identity. If you want to keep your identity same you will have to get the maximum identity before you truncate and reset it to that maximum identity. SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity . If you are not using identity or do not have use for identity you do not have to worry.
TRUNCATE command if not used with BEGIN and END transaction, you will not be able to roll back using log files. Make sure you absolutely do not need the truncated data. SQL SERVER – TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed
Reference : Pinal Dave (http://blog.SQLAuthority.com)