DELETE, TRUNCATE, DROP comparison — SQL

Do you know how to remove records or tables in a database management system (DBMS) using SQL Commands?

DELETE, TRUNCATE, DROP comparison

SQL is a declarative programming language (non-procedure) that focuses more on what outcome we want. TRUNCATE and DROP belong to the Data Definition Language (DDL) component; DELETE belongs to the Data Manipulation Language (DML) component. DELETE is the safe way to specify certain rows to be filtered out without changing the database structure and allows you to ROLLBACK your previous commits. Meanwhile, we need to be considered having a backup when using the DROP command since it would completely remove the whole table in the database. You could not ROLLBACK the DROP since the command auto COMMIT to save changes. Instead, we should seek the TRUNCATE statement to remove only the data and keep the table as an object in the database. Though the DROP command executes the quickest, the TRUNCATE command is the fastest due to the DROP’s complexities.

References

• https://www.analyticsvidhya.com/blog/2022/05/comparison-of-delete-truncate-and-drop-commands-in-sql/
• https://www.geeksforgeeks.org/difference-between-delete-and-drop-in-sql/?ref=rp
• https://www.geeksforgeeks.org/difference-between-delete-and-truncate/
• https://www.geeksforgeeks.org/difference-between-drop-and-truncate-in-sql/