Drop and Truncate are two SQL (Structured Query Language) statements that are used in Database Management Systems, where we wish to remove data records from a database. Both Drop and Truncate statements remove the entire data in a table and the related SQL statement. Delete operation is not effective in this case because it uses more storage spaces than Drop and Truncate.
In case, if we want to discard a table in a database altogether with its all data, SQL allows us to easily perform this using Drop statement. Drop command is a DDL (Data Definition Language) command, and it can be used to destroy an existing database, table, index or view. It deletes the entire information in a table, as well as the table structure from the database. Also, we may wish to get rid of all the data in a table simply, but without the table, and we can use Truncate statement in SQL in such a scenario. Truncate is also a DDL command and it eliminates all the rows in a table but preserves the table definition same for future use.
Drop command
As mentioned earlier, Drop command removes the table definition and all its data, integrity constraints, indexes, triggers, and access privileges, which was created on that particular table. So it drops the existing object from the database entirely, and the relationships to other tables also will no longer valid after executing the command. Also it removes all the information about the table from data dictionary. Following is the typical syntax for using the Drop statement on a table.
DROP TABLE <table_name>
We have to simply replace the table name that we want to remove from database in the above exemplar of Drop command.
It is important to point out that Drop statement cannot be used to delete a table, which has been already referenced by a foreign key constraint. In that case, the referencing foreign key constraint, or that particular table should have to be dropped first. Also, Drop statement cannot be applied on the system tables in the database.
As Drop command is an auto commit statement, the operation once fired cannot be rolled back and no triggers will be fired. When a table is dropped, all the references to the table will not be valid, and so, if we want to use the table again, it has to be recreated with all the integrity constraints and access privileges. All the relationships to the other tables, also has to be located again.
Truncate command
Truncate command is a DDL command, and it removes all the rows in a table without any user specified conditions, and releases the space used by the table, but the table structure with its columns, indexes and constraints remain same. Truncate eliminates data from a table by deallocating the data pages used to store the table data, and only these page deallocations are kept in the transaction log. So it utilizes fewer transaction log resources and system resources compared to other related SQL commands like Delete. So Truncate is a little bit faster statement than others. Following is the typical syntax for Truncate command.
TRUNCATE TABLE <table_name>
We should replace the table name, from which we want to remove the entire data, in the above syntax.
Truncate cannot be used on a table that has been referenced by a foreign key constraint. It uses a commit automatically before it acts and another commit afterward so rollback of the transaction is impossible, and no triggers are fired. If we want to reuse the table we only need to access the existing table definition in the database.
What is the difference between Drop and Truncate?
Both Drop and Truncate commands are DDL commands and also auto commit statements so the transactions performed using these commands cannot be rolled back.
The primary difference between Drop and Truncate is that the Drop command removes, not only all the data in a table, but also it removes the table structure permanently from the database with all references, while the Truncate command only removes all the rows in a table, and it preserves the table structure and its references.
If a table is dropped, the relationships to other tables will be no longer valid, and the integrity constraints and access privileges also will be removed. So if the table is required to reuse, it has to be reconstructed with the relationships, integrity constraints, and also the access privileges. But if a table is truncated, the table structure and its constraints remain for future use, and so, any of the above recreations are not required for reusing.
When these commands are applied, we must be cautious to use them. Also, we should have a better understanding on the nature of these commands, how they work, and also some careful planning before using them in order to prevent from missing essentials. Finally, both of these commands can be used to clean up the databases quickly and easily, consuming fewer resources.