Advertisement
Advertisement


Fastest way to delete all the data in a large table


Question

I had to delete all the rows from a log table that contained about 5 million rows. My initial try was to issue the following command in query analyzer:

delete from client_log

which took a very long time.

2008/10/23
1
49
10/23/2008 4:36:53 PM

Accepted Answer

Check out truncate table which is a lot faster.

2017/01/16
81
1/16/2017 10:40:29 AM


There is a common myth that TRUNCATE somehow skips transaction log.

This is misunderstanding, and is clearly mentioned in MSDN.

This myth is invoked in several comments here. Let's eradicate it together ;)

2008/09/15

For reference TRUNCATE TABLE also works on MySQL

2018/09/11

I use the following method to zero out tables, with the added bonus that it leaves me with an archive copy of the table.

CREATE TABLE `new_table` LIKE `table`;
RENAME TABLE `table` TO `old_table`, `new_table` TO `table`;
2008/09/15

forget truncate and delete. maintain your table definitions (in case you want to recreate it) and just use drop table.

2008/09/15

truncate table is not SQL-platform independent. If you suspect that you might ever change database providers, you might be wary of using it.

2008/09/15

Source: https://stackoverflow.com/questions/64117
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]