Get Prepared for SQL Interview Questions

Know the Difference between TRUNCATE, DELETE, and DROP

Most commonly asked interview questions in SQL.

Truncate, Delete and Drop

The difference between TRUNCATE, DELETE and DROP is one of the most common interview questions that are focused on SQL. We’ll learn their differences and syntax through the medium of this article.

TRUNCATE:

The TRUNCATE TABLE the command deletes the data inside a table, not the table itself. TRUNCATE deletes all the rows of a table at once. It only logs once in the transaction log.

The following SQL truncates the table ‘table_name’:

TRUNCATE TABLE table_name;

Few Important Points:

• TRUNCATE is a Data Definition Language(DDL) command.
• TRUNCATE is executed using a table lock and the whole table is locked to remove all records.
• TRUNCATE removes all rows from a table at once.
• WHERE clause cannot be used with TRUNCATE.
• It is faster performance-wise than DELETE as it only logs once in the transaction log.
• TRUNCATE cannot be used with indexed views.
• To use Truncate on a table, ALTER permission is required on the table.

DELETE:

The DELETE query deletes all records from a database table without deleting the table schemas like columns, indexes, etc.

DELETE Syntax:

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

Few Important Points:

• DELETE is a Data Manipulation Language(DML) command.
• DELETE is executed using a row lock mechanism, each row in the table is locked for deletion.
• WHERE clause can be used with the DELETE query to filter out records and then delete them.
• DELETE maintains an entry in the transaction logs for each row deleted. Hence it is slower than TRUNCATE.
• DELETE permission is required on the table to delete the records.
• The DELETE can be used with indexed views.

DROP:

The DROP TABLE statement is used to drop an existing table in a database. DROP TABLEquery removes the table definition and all data, indexes, triggers, constraints, and permissions for that table.

DROP Syntax:

DROP TABLE table_name;

Few Important Points:

• DROP is a Data Definition Language(DDL) command.
• DROP command removes a table from the database.
• All the table’s rows, indexes, and privileges will also be removed.
• No Data Manipulation Language(DML) triggers will be fired.

DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

Refresher on DDL and DML and their differences:

Definition for DDL:

DDL commands deal with descriptions of the database schema and are used to create and modify the structure of database objects(eg. tables) in the database.

Definition for DML:

DML commands deal with the manipulation of data present in the database.

Differences:

DDL (CREATE, ALTER, TRUNCATE, DROP, COMMENT, etc) commands once executed cannot be rollback/undone whereas DML(INSERT, UPDATE, DELETE) can be rollback.

That’s all for this article. Hope you enjoyed it !!!

Follow Vikram Gupta for Similar Content.

You may like to read similar SQL Interview Questions.

1. Know the Difference between TRUNCATE, DELETE, and DROP

Know the Difference between TRUNCATE, DELETE, and DROP

2. Group by and Having Clauses in SQL

Group by and Having Clauses in SQL

3. Views in SQL — Create, Update, and Drop

Views in SQL — Create, Update, and Drop

4. Indexes When to Use and When to Avoid In SQL.

Indexes When to Use and When to Avoid In SQL.