Truncate (SQL)

In SQL, the TRUNCATE statement removes all the data from a table. The TRUNCATE statement is not actually a part of the SQL standard, but many relational database management systems implement it. A TRUNCATE my_table; is is equivalent in function to a DELETE FROM my_table; statement. However, on some systems, it is implemented differently. For example, in Oracle the truncate statement implies a commit and cannot be undone.

The syntax for issuing a truncate command on a table could be implemented as: Truncate Table [schema][.] <table_name>;

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. Notable exception from this rule is PostgreSQL's implementation, where TRUNCATE is transactional - old version of the whole table is kept until COMMIT or ROLLBACK is issued. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent. TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the index and recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

e.g. Truncate Table [schema][.] <table_name>;

Cannot truncate table <table_name>