Bulk delete (truncate vs delete)

sql sql-server performance truncate bulk
Paulin Shah picture Paulin Shah · Nov 20, 2009 · Viewed 9k times · Source

We have a table with a 150+ million records. We need to clear/delete all rows. Delete operation would take forever due to it writing to the t-logs and we cannot change our recovery model for the whole DB. We have tested the truncate table option.

What we realized that truncate deallocates pages from the table, and if I am not wrong makes them available for reuse but doesn't shrink the db automatically. So, if we want to reduce the DB size, we really would need to do run the shrink db command after truncating the table.

Is this normal procedure? Anything we need to be careful or aware about, or are there any better alternatives?

Answer

mjv picture mjv · Nov 20, 2009

"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.

Related questions

When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More
How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005

Read More
SQL Server SELECT LAST N Rows

This is a known question but the best solution I've found is something like: SELECT TOP N * FROM MyTable ORDER BY Id DESC I've a table with lots of rows. It is not a posibility to use that query because …

Read More