ProgrammerGuide.Net | Step by Step Programmer Guide

DIFFERENCE BETWEEN DELETE COMMAND AND TRUNCATE COMMAND

Both the commands Delete and Truncate helps in the process of data deletion from a database and are in a very close manner similar to each other but they also have their own distinctive points. This comprises of a very important question in any interview round so in order to clear any sort of confusion, let’s study in detail about their main distinctive points.

What is a DELETE command?

Delete command is a type of Data Manipulation Command (DML) which is used to remove as many rows of records as required from a database or any given table. Basically, it removes data from a table without resetting its identity. It comes with a WHERE clause which can be used to delete any number of rows which cater to the conditions provided by the user.

Another important thing to be mentioned here is that deleted data can be restored using the COMMIT or ROLLBACK statement with this command. It clarifies that we have a backup of our database before executing this command.

SYNTAX OF DELETE COMMAND:

DELETE FROM {Name of the table} WHERE {applicable condition}

What is a TRUNCATE command?

Truncate command is a type of Data Definition Language Command (DDL) which is used to delete all the existing rows present in a table. WHERE clause cannot be used with this TRUNCATE command as we don’t really have a choice of filtering our requirements as it deletes all the rows from any given table. This command records the transaction log with regard to every deleted data page. It is also faster in comparison to the DELETE command.

SYNTAX OF TRUNCATE COMMAND:
TRUNCATE TABLE {Table Name}

MAIN DISTINCTIVE POINTS BETWEEN DELETE AND TRUNCATE

  1. DELETE is of DML type whereas TRUNCATE is of DDL type.
  2. DELETE is used when our motive is delete only some parts of a table whereas TRUNCATE is used to delete a table entirely.
  3. TRUNCATE is faster in comparison to DELETE.
  4. TRUNCATE de-allocates data pages in transaction logs whereas DELETE de-allocates rows instead.
  5. WHERE clause can be used with DELETE and cannot be used with TRUNCATE.
  6. DELETE only deletes records or rows from a table whereas TRUNCATE reset’s a table’s entire identity.
  7. Records deleted using TRUNCAT cannot be recovered back at all whereas we can recover deleted records using DELETE.
  8. DELETE activates all delete triggers on the table to fire. However, no triggers are fired on the truncate operation because it does not operate on individual rows.
  9. DELETE performs deletion row-by-row at a time from a table whereas TRUNCATE operates on data pages as it deleted entire table data at a time.
  10. DELETE requires more locks and database resources because it acquires the lock on every deleted row. In contrast, TRUNCATE acquires the lock on the data page before deleting the data page; thus, it requires fewer locks and few resources.

DELETE V/S TRUNCATE : A COMPARISON BASED ON PARAMETERS

SERIAL NUMBERPARAMETERSDELETETRUNCATE
1 DEFINITIONUsed to remove single or multiple records from a table depending on the conditions.Used to remove complete data from a table but not the table itse.lf, preserving the structure of the table.
2 LANGUAGE TYPEData Manipulation Language (DML) type.Data Definition Language (DDL) type
3 WHERE CLAUSEWHERE clause can be used to filter any specific row or data from a table.WHERE clause cannot be used with this command.
4 LOCKINGLocks the row before deletion.Locks the data page before deletion.
5 PERMISSIONDELETE permission is required to use this commandALTER permission is required to use this command
6 SPEEDSlower as comparedFaster as compared
7 RESTOREDeleted data can be restored using COMMIT or ROLLBACK statement.Deleted data cannot be restored.
8 REMOVAL SPEEDEliminates records one-by-one.Eliminates all the records at once.
9 TABLE IDENTITYIt doesn’t reset the table’s identity because it only deleted the data.Always resets the table identity.
10 SPACEIt occupies more transaction space because it maintains a log for each deleted row.It occupies less transaction space as it maintains a transaction log for entire data page instead of each row.

Please click here for related products on Amazon!

Jayashee

Add comment

Want to Check Celebrities News?