Difference between drop, delete and truncate in SQL database is frequently asked interview question. DELETE removes rows one by one depending upon WHERE condition or delete all rows if there is no WHERE condition. TRUNCATE removes all rows at once and DROP command removes a table or database completely from database.
In some situations, it may be confusing that which one to use DELETE without WHERE clause or TRUNCATE to remove all the records as both remove all the records from the table, so which one is the best option to use etc. You should find the answer after introduction of these statements.
DELETE:
- Removes rows from a table. Delete rows one at a time & records an entry in the transaction log for each deleted row.
- Deleted data can be rollback.
- DML command
- When the DELETE statement is executed using a row lock, each row in the table is locked for deletion.
TRUNCATE:
- TRUNCATE removes all rows from a table.
- TRUNCATE TABLE always locks the table and page but not each row.
- If we truncate a table, then truncate table statement cannot be rolled back in some of the database.
- Truncate table statement is a Data Definition Language.
DROP:
The DROP in SQL command removes a table from the database
Sub Interview Question:
Interviewer:Which one is faster DELETE or TRUNCATE command if we want to remove all rows from a table?
Answer: TRUNCATE is faster that DELETE command as DELETE command put a lock for each rows while deleting the rows. Whereas, TRUNCATE command locks the table not rows and remove all rows.
Interviewer: What is type of TRUNCATE command DDL or DML? ( Read DDL commands and DML commands)
Answer: DDL – Data Definition Language.
NOTES:
Delete Drop and Truncate – SQL Example
Consider a table COURSE with column ID, NAME and DURATION in days to finish the course.
| ID | NAME | DURATION |
| 1 | C++ | 10 |
| 2 | JAVA | 15 |
| 3 | C# | 10 |
| 4 | PYTHON | 20 |
Let’s create the COURSE table and populate the data
CREATE TABLE COURSE(ID int Primary key,NAME VARCHAR(50),DURATION INT)
INSERT INTO COURSE VALUES(1,'C++',10)
INSERT INTO COURSE VALUES(2,'JAVA',15)
INSERT INTO COURSE VALUES(3,'C#',10)
INSERT INTO COURSE VALUES(4,'PYTHON',20)
DELETE with WHERE clause:
If we use DELETE command with WHERE clause in the query the specific existing record (row) will be deleted from the table.
Query:
DELETE from COURSE Where DURATION = 15
If we execute the above query, the Java course (entire row) will be deleted from the table.
OUTPUT:
| ID | NAME | DURATION |
| 1 | C++ | 10 |
| 3 | C# | 10 |
| 4 | PYTHON | 20 |
DELETE without WHERE clause:
If we use delete command without WHERE clause, all the rows will be deleted from the table.
Query:
DELETE from COURSE
This query will delete all rows. From the output below, you can see that it has deleted only records not the table definition (table structure). Means table COURSE is still in the database.
OUTPUT:
| ID | NAME | DURATION |
Let’s fill the records again in the table COURSE TO perform SQL TRUNCATE Statement
INSERT INTO COURSE VALUES(1,'C++',10)
INSERT INTO COURSE VALUES(2,'JAVA',15)
INSERT INTO COURSE VALUES(3,'C#',10)
INSERT INTO COURSE VALUES(4,'PYTHON',20)
SQL TRUNCATE Example:
TRUNCATE will delete all records from the table COURSE.
Query:
TRUNCATE TABLE COURSE;
OUTPUT:
| ID | NAME | DURATION |
NOTE: DELETE without WHERE clause and TRUNCATE doing the same operation. But difference is that delete putting lock on every rows then remove records, whereas TRUNCATE is not putting lock on every row but on entire table then remove all records. So, there will be performance difference.
SQL DROP Example:
The SQL DROP TABLE statement will remove the complete table definition or say table structure from the database and hence the record. Now there is no table named COURSE in the database. If we want, we need to create the new table again.
Query:
DROP TABLE COURSE;
After execution of this query no COURSE TABLE will be available in the database
In fact, The DROP command will remove a table definition and data, indexes, triggers, constraints, and permission specifications for that table.
NOTE: In case of TRUNCATE and DROP, TRUNCATE does not remove the table structure but DROP will remove the table structure.