In SQL, the TRUNCATE TABLE statement is a Data Manipulation Language (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, typically bypassing a number of integrity enforcing mechanisms). The TRUNCATE TABLE statement was officially introduced in the SQL:2008 standard, as the optional feature F200, "TRUNCATE TABLE statement").
Here are some key features of the TRUNCATE TABLE statement:
- TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain).
- TRUNCATE TABLE is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause)).
- TRUNCATE TABLE 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).
- Records removed by TRUNCATE TABLE cannot be restored in a rollback operation).
- TRUNCATE TABLE is typically extremely fast, making it perfect for removing data from a temporary table.
It is important to note that TRUNCATE TABLE and DELETE commands work similarly logically, but there are some major differences between them. For example, TRUNCATE TABLE removes all of the rows from a table, regardless of whether or not any conditions are met, while DELETE removes one or more rows from a table based on the conditions specified in a WHERE Clause. Additionally, TRUNCATE TABLE always locks the table and page but not each row, while DELETE statement is executed using a row lock, and each row in the table is locked for deletion.
In summary, TRUNCATE TABLE is a SQL command that deletes all rows of a table without causing a triggered action, and it is typically faster than the DELETE command.
