User:Gelbard/Sandbox

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.

Usage

The basic truncate statment has this syntax:

TRUNCATE TABLE table_name

The syntax may differ between databases. For example, in Oracle Database the syntax is as mentioned above, but in DB2 the statement lacks the word TABLE.

Difference between TRUNCATE and DELETE

Unlike the DELETE statement, which is a DML type statement, the TRUNCATE statement is a DDL type statement, and therefore it differs from the seemingly same statement: DELETE FROM table_name.

The differences are as follows:

DB2

• The TRUNCATE statement can ignore Delete triggers.
• The TRUNCATE statement can perform immediate COMMIT.
• The TRUNCATE statement can keep the storage allocated to the table.

Oracle

• Oracle database automatically performs a COMMIT before and after the TRUNCATE statement.
• The TRUNCATE statement can drop all the storage allocated to the table.
• The TRUNCATE statement can reset the high-water mark for the table.
• The TRUNCATE statement ignores triggers.
• A TRUNCATE statement can not be issued on a table which is a part of a cluster.
• A TRUNCATE statement can not be issued on the parent table of an enabled foreign key constraint.

See also

In SQL Truncate statement doesn't delete the content of a table which is to be truncated while it removes the address of the table from the list containing addresses to identify the table. That's why Truncate statement is 300 times faster than Delete statement of SQL because Delete statement removes the content of table one-by-one.

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

[[Category:SQL statements]] {{compu-lang-stub}}