Estimated reading time: 2 minutes
DROP and TRUNCATE are two SQL commands that can be used to remove data from a table, but they work in different ways:
How to drop a table from a database
- DROP: The DROP command removes a table from the database. When you use DROP, you permanently delete the entire table, along with all of its data and the structure itself. The syntax for DROP is:
DROP TABLE table_name;
It is important to remember that if you have the same name spread over a few databases, you should reference the correct databases, to ensure you do not drop the wrong table.
Also, this cannot be undone, so if you need to restore a table, make sure you have a backup before you start.
Finally, you need to make sure you have the correct permissions for this, as not everyone will.
Also as a DBA(database administrator), you should clearly define when granting permissions do they need this level of access.
In the above example, “table_name” is the name of the table you want to drop.
How to Truncate a table in a database
- TRUNCATE: The TRUNCATE command also removes data from a table, but it preserves the structure of the table. When you use TRUNCATE, you delete all of the data from the table, but the table structure remains intact. This means that you can still insert new data into the table after using TRUNCATE. The syntax for TRUNCATE is:
TRUNCATE TABLE table_name;
For this example, “table_name" is the name of the table you want to truncate.
In summary, the main difference between DROP and TRUNCATE is that DROP permanently deletes the entire table, including the structure, while TRUNCATE deletes all the data from the table while preserving the structure.