Truncating is the process of cutting off or shortening something. It can be used in a variety of contexts, from taking a walk that is cut short due to rain to deleting all records from a database table. In the world of SQL, the TRUNCATE TABLE statement is used to delete all records in a table. It performs the same function as a DELETE statement without a WHERE clause.
The TRUNCATE TABLE command is used to delete complete data from an existing table. It is a data definition language (DDL) statement that flushes an existing table by deleting all records and preserving table columns, privileges, indexes, views, constraints, relationships, etc. It is equivalent to but faster than the DELETE statement in SQL. However, unlike DELETE, it cannot be undone.
It's important to note that the TRUNCATE TABLE statement does not require confirmation at every step, but automatically triggers confirmation at the end of the statement execution. Therefore, it's important to be careful when using it. To understand how the TRUNCATE TABLE statement works, let's consider a “customers” table. The data in the table looks like this.
Next, let's run the TRUNCATE TABLE statement on the client's table to delete all of its records. We can do this using the following SQL query: TRUNCATE TABLE customer_details;In the following figure we can see that the TRUNCATE TABLE statement deleted all records from the client table. However, all columns, relationships, indexes, and table structures have been securely maintained. It's also important to note that there are differences between the SQL TRUNCATE TABLE statement, the SQL DELETE TABLE statement, and the SQL DROP TABLE statement in SQL. To illustrate this difference, let's run TRUNCATE TABLE on the customer_details table and DROP TABLE on the student's table and then check the difference. We can see in the previous images that the DROP TABLE statement is faster than the TRUNCATE TABLE statement in SQL.
To delete all data from an existing table, use the SQL TRUNCATE TABLE command. You can also use the DROP TABLE command to delete an entire table. However, Truncate will delete the entire table structure from the database and you will have to recreate the table if you want to store data. In conclusion, truncating is a useful tool for deleting all records from a database table without deleting the table itself.
It is equivalent to but faster than the DELETE statement in SQL and less space is used in the transaction log.
