Menu
I have this table in a SQL Server 2008 R2 instance which I have a scheduled process that runs nightly against it. The table can have upward to 500K records in it at any one time. After processing this table I need to remove all rows from it so I am wondering which of the following methods would produce the least overhead (ie Excessive Transaction Log entries):
Deleting the contents of the table is out due to time and extra Transaction log entries it makes.
The consensus seems to be Truncation, Thanks everyone!
861
To remove all rows from a large table and leave the table structure, use TRUNCATE TABLE . It's faster than DELETE . To remove an entire table, including its structure and data, use DROP TABLE .
No. TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary.
The DROP command is used to remove table definition and its contents. Whereas the TRUNCATE command is used to delete all the rows from the table.
In the DROP query, deleted space is not used. The deleted space is used but less than the DELETE statement. The DROP query deletes data quickly, but there are so many complications. The TRUNCATE query in SQL is faster than the DROP query.
TRUNCATE TABLE is your best bet. From MSDN:
Removes all rows from a table without logging the individual row deletes.
So that means it won't bloat your transaction log. Dropping and creating the table not only requires more complex SQL, but also additional permissions. Any settings attached to the table (triggers, GRANT or DENY, etc.) will also have to be re-built.
170
Truncating the table does not leave row-by-row entries in the transaction log - so neither solution will clutter up your logs too much. If it were me, I'd truncate over having to drop and create each time.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
