TRUNCATE TABLE statement is used to delete a table permanently. It deletes all the records from the table.
Syntax :
TRUNCATE [TABLE] [database_name.]table_name; ​
Difference between DELETE and TRUNCATE statement :
* DELETE statement is used to remove one or more columns from a table as well as the whole table. On the other hand, the TRUNCATE TABLE statement is used to delete the whole table permanently.
* TRUNCATE TABLE statement is same as DELETE statement without a WHERE clause.
* DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
* TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and record only the page deallocations in the transaction log. Hence it is faster than delete statement.
Example :
Let's truncate the table "Students".
TRUNCATE TABLE javatpoint.Students;
Output :
Query OK, 0 rows affected (0.031sec).
The TRUNCATE query is executed successfully. You can see that the records of "Student" table have been deleted permanently.
SELECT * FROM Students; ​
Output :
No record found
