5 pts.
 Truncate or not Truncate SQL
What are the pros/cons of truncate a table export/import to just drop table?

Software/Hardware used:
ASKED: April 11, 2008  3:37 PM
UPDATED: April 14, 2008  4:01 PM

Answer Wiki:
Truncate deletes all your data (w/o using rollback/undo) retaining the definition of the table (and the storage it used, if desired). Dropping the table deletes the data (w/o using rollback/undo) and removes the definition from the data dictionary and de-allocates the storage that was assigned to the table. Which one you want to use depends on what you are trying to accomplish. If you are reusing the table, truncate clears the table and the REUSE STORAGE clause retains the storage assigned to the table and it runs faster than a drop table. If it is a one-time use table, drop the table. If you are going to reuse the table truncate is faster and you don't have to recreate the table everytime. Depending on your application, you may want to look at global temporary tables to do this same thing. They get cleaned up when your session ends automatically. -Dave
Last Wiki Answer Submitted:  April 14, 2008  2:23 pm  by  Dwaltr   900 pts.
All Answer Wiki Contributors:  Dwaltr   900 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _


 

Another reason to truncate rather than drop the table is that if you drop the table, you lose the permissions, indexes, constraints, etc. If you have those requirements, you would need to set that back up each time you create the table.

 1,740 pts.