Drop vs. Truncate

Difference Between Drop and Truncate

Drop and Truncate are the structured query languages or SQL statements used in Database Management System. They both remove the data in a table. SQL easily allows us to discard a table in a database completely with all its data. It makes use of Drop statement for this purpose. Drop is a Data Definition Language command. It can delete the whole information of a table along with the structure of the table from the database. Truncate statement in SQL may be used to delete the information only and keep the table intact. Truncate is a DDL command which can be used to delete all rows of a table and to keep the table intact for use in future.

Drop command

The Drop Command removes the table and all its data completely. Data, indexes, integrity constraints and indexes are completely removed. Thus an object is entirely removed. Its relation to the other tables will be snapped when the command is executed. It also deletes the information of the table from the dictionary. Drop statement makes use of the following syntax:

DROP TABLE <table_name>

In Drop Command, the table name is replaced that is to be removed from the database in the manner shown above.

It should be kept in mind that Drop statement cannot delete a table if it has been already referenced by a foreign key constraint. In this case, the referencing foreign key constraint has to be dropped first. Drop statement cannot be used in the system tables in the database. If the operation is once started, it cannot be held back because Drop Command is an auto commit statement. Once a table is deleted, no reference to the table remains valid. If the table is needed again, it has to be created afresh and relations to the other tables have to be relocated.

Truncate command

The Truncate command removes all the rows of a table and releases the space occupied by that table. But the table structure remains the same. Truncate deletes the data of a table by de-allocating the pages used in staring the data of the table. Only these de-allocated pages are kept in transaction. The Truncate uses a fewer transaction log resources as compared to other related SQL command like Delete. Thus, it is a faster statement. It uses the following syntax.

TRUNCATE TABLE <table name>

The table name is replaced as shown above. Like Drop, Truncate cannot operate upon a table which has key constraint references. Once started, its roll back is not possible as it uses commit automatically. No triggers can be fired. If one wants to reuse the table, he has to access the table existing in the database.

Difference between Drop and Truncate

Both Truncate and Drop are the DDL commands. They are auto commit statements, so they cannot be rolled back in case they have been started. The basic difference is that Drop removes all the data in the table along with the structure of table permanently whereas Truncate removes only rows of a table. It keeps the table structure and its references intact.

The relations of the table with others are rendered invalid once the table is deleted. Access privileges and integrity constraints are removed as well. If someone wants to reuse the table, it has to be created afresh along with integrity constraints and access privileges. But in Truncate, the structure of the table is saved for future use.

 

Category: VS  |  Tags: