Truncating a table is a great way to quickly delete all the data from an existing table without deleting the table itself. It is an efficient way to delete data from a table, especially if the table has numerous triggers, indexes, and other dependencies. Truncating a table can be faster than deleting all rows with the DELETE statement and does not affect any of the table's indexes, triggers, or dependencies. In this article, we will discuss what truncating a table is, how to use the TRUNCATE TABLE statement to delete all data from a large table quickly, and the differences between the TRUNCATE TABLE and DELETE statements.
The TRUNCATE TABLE command is used to delete complete data from an existing table. It removes all rows from a table, but not the table itself. The structure of the table and its columns, constraints, indexes, etc. remain intact.
To delete the table definition in addition to its data, you can use the DROP TABLE statement. The SQL TRUNCATE TABLE statement is used to delete all records in a table. It performs the same function as a DELETE statement without a WHERE clause. Deleting rows with the TRUNCATE TABLE statement can be more efficient than deleting and re-creating a table.
Dropping and re-creating a table invalidates the table-dependent objects, requires that object privileges be re-granted on the table, and indexes, integrity constraints, and triggers must be re-created on the table and their storage parameters re-specified. Truncation has none of these effects. Deleting rows with the TRUNCATE TABLE statement can be faster than deleting all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies. You can truncate tables organized by indexes and temporary tables.
When you truncate a temporary table, only rows created during the current session are deleted. It is important to note that TRUNCATE will not trigger the DELETE trigger. Can't undo a TRUNCATE TABLE statement either. When the TRUNCATE TABLE statement is issued, the database system deletes all rows from the table by deallocating the data pages allocated by the table. In this way, it reduces the resources for logging and the number of locks that need to be acquired. Tables with more than 128 extensions can be deleted or truncated in Microsoft SQL Server without requiring simultaneous locks on all extensions required for deletion.
Below is an example of how much record usage is reduced when data is deleted from using the TRUNCATE statement. If you specify CASCADE when truncating, Oracle Database truncates all child tables that reference a table with an ON DELETE CASCADE referential constraint enabled. This is a recursive operation that will truncate all child tables, granchild tables, etc., using the specified options. Oracle Database changes the NEXT storage parameter of the table to be the size of the last extent removed from the segment in the truncation process. Logically, the TRUNCATE TABLE statement and the DELETE statement without WHERE clause have same effect as deleting all data from a table. Now you know how to use TRUNCATE TABLE statement to delete all data from a large table quickly and understand differences between TRUNCATE TABLE and DELETE statements.
Truncating a table can be faster and does not affect any of its indexes, triggers or dependencies.
