In
SQL, the
TRUNCATE TABLE statement is a
Data Manipulation Language
A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database. A DML is often a sublanguage of a broader database language such as SQL, with the DML com ...
(DML) operation that deletes all rows of a table without causing a triggered action. The result of this operation quickly removes all data from a
table
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the
SQL:2008 standard, as the optional feature F200, "TRUNCATE TABLE statement".
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the
DROP TABLE statement.
The
TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the
DELETE FROM mytable statement (without a
WHERE
Where may refer to:
* Where?, one of the Five Ws in journalism
* where (command), a shell command
* Where (SQL), a database language clause
* Where.com, a provider of location-based applications via mobile phones
* ''Where'' (magazine), a serie ...
clause). The following characteristics distinguish
TRUNCATE TABLE from
DELETE:
* In the
Oracle Database
Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for running online ...
,
TRUNCATE is implicitly preceded and followed by a
commit operation. (This may also be the case in
MySQL
MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database ...
, when using a transactional storage engine.)
* Typically,
TRUNCATE TABLE quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of