SQL Truncate Table is one of the Data Definition Language (DDL) commands. It deletes all the records in a database table. However, it does not delete the table’s structure; instead, it simply reconstructs the structure. The table’s original structure is retained while all the records are removed. Because it reconstructs the structure of a database, it is considered to be a DDL command instead of a DML command.
The DROP table command removes both the structure and the contents of the table. This is where the DROP command differs from the truncate command. Truncate is similar to DELETE in that it deletes the table’s structure and contents. The only difference is that it uses a WHERE clause instead of DELETE and executes faster.
Why do we need to use the truncate command?
We may have saved the wrong data in the database and now we need to change it while preserving the structure of the table so that we don’t have to rebuild the table.
TRUNCATE Command
Below mentioned is the syntax of Truncate Command.
Syntax
TRUNCATE TABLE table_name;TRUNCATE vs DELETE
Even though the TRUNCATE and DELETE commands work similar logically, there are some major differences that exist between them. They are detailed in the table below.
| DELETE | TRUNCATE |
| The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause. | SQL’s TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met. |
| It is a DML(Data Manipulation Language) command. | It is a DDL(Data Definition Language) command. |
| There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed. | When you use the TRUNCATE command, the modifications made to the table are committed automatically. |
| It deletes rows one at a time and applies same criteria to each deletion. | It removes all of the information in one go. |
| The WHERE clause serves as the condition in this case. | The WHERE Clause is not available. |
| All rows are locked after deletion. | TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted. |
| It makes a record of each and every transaction in the log file. | The only activity recorded is the deallocation of the pages on which the data is stored. |
| It consumes a greater amount of transaction space compared to TRUNCATE command. | It takes comparatively less amount of transaction space. |
| If there is an identity column, the table identity is not reset to the value it had when the table was created. | It returns the table identity to a value it was given as a seed. |
| It requires authorization to delete. | It requires table alter permission. |
| When it comes to large databases, it is much slower. | It is much faster. |
TRUNCATE vs DROP
Unlike TRUNCATE that resets the table structure, DROP command completely frees the table space from the memory. They are both Data Definition Language (DDL) operations as they interact with the definitions of database objects; which allows the database to automatically commit once these commands are executed with no chance to roll back.
However, there are still some differences exist between these two commands, which have been summarized in the following table −
| DROP | TRUNCATE |
| The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc. | The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition. |
| It is a DDL(Data Definition Language) command. | It is also a DDL(Data Definition Language) command. |
| The table space is completely freed from the memory. | The table still exists in the memory. |
| All the integrity constraints are removed. | The integrity constraints still exist in the table. |
| Requires ALTER and CONTROL permissions on the table schema and table respectively, to be able to perform this command. | Only requires the ALTER permissions to truncate the table. |
| DROP command is much slower than TRUNCATE but faster than DELETE. | TRUNCATE command is faster than both DROP and DELETE commands. |
Conclusion
The TRUNCATE TABLE statement in SQL is a powerful command used to quickly and efficiently delete all rows from a table while retaining the table structure. Unlike the DELETE statement, which removes rows one by one and logs each deletion, TRUNCATE TABLE is much faster as it deallocates the data pages used to store the table’s data, effectively resetting the table to an empty state in a single operation.
In conclusion, TRUNCATE TABLE is a useful SQL command for quickly clearing the contents of a table without dropping and recreating the table itself. However, it’s important to note that TRUNCATE TABLE cannot be rolled back, and it resets the table’s identity column seed value to its original starting point. Additionally, it requires appropriate permissions and should be used with caution, especially in production environments where data integrity is crucial.
Frequently Asked Questions
The TRUNCATE TABLE statement is used to quickly remove all rows from a table, effectively resetting the table to its initial state without dropping and recreating the table structure.
Unlike the DELETE statement, which removes rows one by one and logs each deletion, TRUNCATE TABLE deallocates the data pages used to store the table’s data in a single operation, making it much faster and more efficient, especially for large tables.
No, the TRUNCATE TABLE operation cannot be rolled back. Once executed, it permanently removes all data from the table.
Yes, when you use TRUNCATE TABLE, it resets the identity column seed value to its original starting point, effectively reinitializing any identity columns in the table.
Typically, you need ALTER permissions on the table or CONTROL permissions on the schema to execute TRUNCATE TABLE. It’s essential to ensure that users have the appropriate permissions before allowing them to truncate tables, especially in production environments.
