Bulk delete (truncate vs delete) Bulk delete (truncate vs delete) sql-server sql-server

Bulk delete (truncate vs delete)


truncate is what you're looking for. If you need to slim down the db afterwards, run a shrink.

This MSDN refernce (if you're talking T-SQL) compares the behind the scenes of deleting rows versus truncating.


"Delete all rows"... wouldn't DROP TABLE (and re-recreate an empty one with same schema / indices) be preferable ? (I personally like "fresh starts" ;-) )

This said TRUNCATE TABLE is quite OK too, and yes, DBCC SHRINKFILE may be required afterwards if you wish to recover the space.


Depending on the size of the full database, the shrink may take a while; I've found it to go faster if it is shrunk in smaller chunks, rather than trying to get it back all at once.