ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Differences Between the TRUNCATE, DROP and DELETE Commands

Updated on May 23, 2014

People learning database software, or interviewing for database-related jobs, need to know the differences between the TRUNCATE, DROP and DELETE commands in SQL and related tools: Oracle, MySQL, and SQL Database (Microsoft Database). Even though you might not use these commands often in practice, you have to know how they differ.

In a job interview, for example, you may be asked:

  • What are the differences between the DROP and TRUNCATE commands?
  • Why do programmers use the DELETE and DROP commands, and what are some differences between them?

This page explains the TRUNCATE, DROP and DELETE commands and gives examples. A chart below summarizes the differences between the three commands.

DELETE

The DELETE command is used to delete rows (all rows, or a specific number of rows) from a database table. To remove all rows, we use wildcard (*) characters; to remove specific rows, we use the WHERE condition. DELETE is a DML (Data Manipulation Language) operation, and using it fires a “trigger” (that is, it sends a command to delete information from other tables). That means that if we mistakenly delete some rows, we can recover those using the TRUNCATE command. After completing the delete operation, we have to use COMMIT or TRUNCATE command to complete the transaction. COMMIT permanently saves the transaction and TRUNCATE just redoes the transaction.

Example of DELETE command (delete all rows from the table)

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 26 13:16:37 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Personal Oracle Database 10g Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT count(*) from books;

  COUNT(*)
----------
         7

SQL> DELETE from books;

7 rows deleted.

SQL> SELECT count(*) from books;

  COUNT(*)
----------
         0

SQL> 

Example of DELETE command (delete selected number of rows from the table)

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 26 13:25:04 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Personal Oracle Database 10g Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT count(*) from books;

  COUNT(*)
----------
         7

SQL> DELETE from books
  2  where book_id = 002;

1 row deleted.

SQL> SELECT count(*) from books;

  COUNT(*)
----------
         6

SQL> 

Video: DELETE Command in Action

DROP

The DROP command is used to remove a table from the database. It removes the table completely: all the rows, plus the table structure and views. The DROP command does not trigger any DML operation; it is a DDL (Data Definition Language) command. That means DROP cannot be rolled back or undone. So, when you execute a DROP command, be sure that’s what you want to do.

Example of DROP command

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 26 13:35:49 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Personal Oracle Database 10g Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc books;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BOOK_ID                                   NOT NULL NUMBER
 BOOK_NAME                                          VARCHAR2(30)
 AUTHOR_NAME                                        VARCHAR2(40)
 BOOK_ISBN                                          VARCHAR2(20)

SQL> drop table books;

Table dropped.

SQL> desc books;
ERROR:
ORA-04043: object books does not exist

SQL>

Video: DROP Command in Action

TRUNCATE

To remove all the rows from a table, the TRUNCATE command is used. TRUNCATE deletes all the rows, but the table structure remains the same. TRUNCATE is a DDL command and no triggers are fired for this operation. Therefore, like DROP, the TRUNCATE command cannot be rolled back or undone. It is the fastest way to get rid of the rows in a table.

Example of TRUNCATE command

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 26 13:35:49 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Personal Oracle Database 10g Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT count(*) from books;

  COUNT(*)
----------
         7

SQL> TRUNCATE table books;

Table truncated.


SQL> SELECT count(*) from books;

  COUNT(*)
----------
         0

SQL> 

Video: TRUNCATE Command in Operation

Summary: Differences between DELETE, DROP and TRUNCATE

 
DELETE
DROP
TRUNCATE
Usage
Removes rows from a table
Removes a table from the database / data dictionary
Removes all rows from a table
Type of command
DML
DDL
DDL
Rollback
Can be rolled back
Cannot be rolled back
Cannot be rolled back
Rows, indexes and privileges
Only table rows are deleted
Table rows, indexes and privileges are deleted
Table rows are deleted
DML trigger firing
Trigger is fired
No triggers are fired
No triggers are fired
Performance
Slower than TRUNCATE
Quick but could lead to complications
Faster than DELETE
Undo space
Uses "undo" space
Does not use “undo” space
Uses "undo" space, but not as much as DELETE
Permanent deletion
Does not remove the record permanently.
Removes all records, indexes, and privileges permanently.
Removes the record permanently.
Can you write conditions using a WHERE Clause?
Yes
No
No
Row deletion
Deletes all or some rows
Deletes all rows
Deletes all rows
Is table structure changed?
No
Yes
No
SQL command
DELETE FROM <example> WHERE job = 'CLERK'
DROP TABLE <example>
TRUNCATE TABLE <example>

Important Information to Remember

  • TRUNCATE deletes all the rows from a table, DELETE deletes all or some rows reversibly, and DROP deletes the table.
  • DELETE is a DML command; DROP and TRUNCATE are DDL commands.
  • DELETE fires triggers; DROP and TRUNCATE do not.
  • DELETE can be rolled back, but DROP and TRUNCATE cannot.

A Quiz About DROP, DELETE, and TRUNCATE

Try this quiz to see your expertise! Use the screenshot below, about a table named "books," to answer some of the questions.

Use this screenshot to answer some quiz questions
Use this screenshot to answer some quiz questions

Job Interview Questions

Here are some sample questions you may see during an interview or written job exam:

  1. What are the differences between DELETE and TRUNCATE commands in SQL Server?
  2. What are two differences between the TRUNCATE and DROP commands?
  3. What is the difference between dropping a table and truncating a table?
  4. How do you write a “drop table” statement in Oracle?
  5. What is the syntax for a “truncate table” command in Oracle?
  6. Why is the DELETE command used?

You should be able to answer them by studying the material above, including the examples and videos. But if not, please tell me in the comment section below, and I will get you the answers.

Comments

    0 of 8192 characters used
    Post Comment

    • jabelufiroz profile image

      Firoz 3 years ago from India

      Good job. Keep sharing.

    Click to Rate This Article