sql – Which is faster DROP TABLE or TRUNCATE TABLE

Question:

For example, when retrieving a procedure, a temporary table # is created and filled with some data.

If the procedure is started again, then this table variable is DROP and it is re-created and filled with data.

The structure of the table does not change.

Will there be a profit if a normal table is created from a temporary table and TRUNCATE is done instead of DROP?

Answer:

In theory, truncate should be somewhat faster than drop (and even more so than the drop + create pair), since truncate only changes information about the space allocated for storing table data, changing it from occupied to free. Whereas drop does the same thing, but in addition also deletes information about the object (records in the system tables that there is such and such a table with such and such a set of columns).

However, as rightly noted in the comments, in practice, the costs of both one and the other can be negligible compared to other operations in the procedure.

In addition, I would be careful not to

from temporary table create normal table

even if you are sure that there will be no conflicts due to parallel access, since temporary tables "live" in the system tempdb database, and additional measures are taken to improve performance ( link , see Improving tempdb performance ).

Scroll to Top