- HubPages»
- Technology»
- Computers & Software»
- Computer Software
Differences Between the TRUNCATE, DROP and DELETE Commands
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.
Job Interview Questions
Here are some sample questions you may see during an interview or written job exam:
- What are the differences between DELETE and TRUNCATE commands in SQL Server?
- What are two differences between the TRUNCATE and DROP commands?
- What is the difference between dropping a table and truncating a table?
- How do you write a “drop table” statement in Oracle?
- What is the syntax for a “truncate table” command in Oracle?
- 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.
