Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Bulk delete (truncate vs delete)

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?

like image 929
Paulin Shah Avatar asked Nov 20 '09 20:11

Paulin Shah


People also ask

Which is better delete or TRUNCATE?

Truncate removes all records and doesn't fire triggers. Truncate is faster compared to delete as it makes less use of the transaction log. Truncate is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.

Which is faster delete or TRUNCATE?

TRUNCATE is faster than DELETE , as it doesn't scan every record before removing it. TRUNCATE TABLE locks the whole table to remove data from a table; thus, this command also uses less transaction space than DELETE . Unlike DELETE , TRUNCATE does not return the number of rows deleted from the table.

What are 2 differences between delete and TRUNCATE?

Both the SQL DELETE and SQL TRUNCATE commands can be used to remove records from a table. However, the DELETE command employs the WHERE clause to specify rows in a table for deletion action, whereas the TRUNCATE command does not use any clause and deletes rows all at once.

What is the difference between TRUNCATE and delete and drop?

The DELETE command deletes one or more existing records from the table in the database. The DROP Command drops the complete table from the database. The TRUNCATE Command deletes all the rows from the existing table, leaving the row with the column names.


2 Answers

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.

like image 84
ProKiner Avatar answered Oct 21 '22 23:10

ProKiner


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

like image 30
mjv Avatar answered Oct 21 '22 23:10

mjv