Drop vs Truncate
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表
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命令是DDL命令，它在没有任何用户指定条件的情况下删除了表中的所有行，并释放了表格的空间，但是表及其列，索引和约束的表结构保持不变。截断是通过处理用于存储表数据的数据页面来消除表中的数据，并且仅保留这些页面交易。因此，与其他相关的SQL命令（如DELETE）相比，它使用的交易日志资源和系统资源更少。因此，截断比其他人要快一点。以下是截断命令的典型语法。
截断表
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.
如果删除了表，则与其他表的关系将不再有效，并且还将删除完整性约束和访问权限。因此，如果需要该表重复使用，则必须通过关系，完整性约束以及访问权限重建。但是，如果截断了表格，则表结构及其约束仍供将来使用，因此，上述任何娱乐活动都不需要重复使用。
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.
发表评论