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 

      5 years ago from India

      Good job. Keep sharing.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)