Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

In SQL, what is a key difference between Truncate and Delete?

user-image
Question added by Mohammad Awartani , Technical Support Specialist , Arab Bank PLC
Date Posted: 2016/03/09
Rohan  Sharma
by Rohan Sharma , Senior Software Test Engineer , Xavient Information Systems

TRUNCATE command cannot be rollback, it is faster and delete the data including table structure also releases the memory allocated to the table 

DELETE command can be rollback and only the data in the table will be deleted.

Mahmoud ABID
by Mahmoud ABID , Production Planning Engineer , Company Esth’Elle S.A

Delete is the command that only remove the data from the table. It is DML statement. Deleted data can be rollback.

Truncate is the DML command. This command delete the data from table. But there is one difference from ordinary delete command. Truncate command drop the storage held by this table. Drop storage can be use by this table again or some other table. This is the faster command because it directly drop the storage

Naresh Kumar Kutum
by Naresh Kumar Kutum , Executive Associate , Exl services.com pvt ltd

Delete is a simple command to remove a data from a particular format and truncate means removal of a data from a file in shorter and quicker way.

Anu Cheriyan Kochumuttam
by Anu Cheriyan Kochumuttam , Software Engineer , Elinx Infotech LLC

Delete: The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.

Truncate: TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

TRUNCATE is a DDL statement. It issues auto-commit after the exectution of the statement. DELETE keeps records in buffers(temporary storage) till the first ROLLBACK or COMMIT. You can use conditions on DELETE like WHERE etc. You can't using TRUNCATE.

truncate is used to remove all the rows from the table... where delete is used to remove particular rows from the table by using 'where' condition.Truncate is DDL statement and Delete is a DML statement.

The delete command is used to remove rows from a table while Truncate is the need to commit or roll back the transaction to make the changes permanent

Funmilade Oreagba
by Funmilade Oreagba , Enterprise Project Manager , Jaiz Bank Plc

Delete is a DML statement, it is safer as it can be rolled back WHILE Truncate cannot be rolled back. It removes the rows from the table. The good part of truncate is that it creates more storage space

Garvit Anand
by Garvit Anand , Senior Software Quality Assurance , Nagarro Software Pvt. Ltd

Truncate removes all the rows from a table. Delete with a where clause can deleterows (as specified using where clause) from a table.

Ahmed Ibrahim Rihan
by Ahmed Ibrahim Rihan , Software Development Supervisor , Orange - Egypt

TRUNCATE
  • TRUNCATE is executed using a table lock and the whole table is locked for remove all records.
  • We cannot use Where clause with TRUNCATE.
  • TRUNCATE removes all rows from a table.
  • Minimal logging in a transaction log, so it is performance wise faster.
  • TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

DELETE

  • DELETE is executed using a row lock, each row in the table is locked for deletion.
  • We can use where clause with DELETE to filter & delete specific records.
  • The DELETE command is used to remove rows from a table based on WHERE condition.
  • It maintains the log, so it slower than TRUNCATE because DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

Truncate is a DDL command and Delete is a DML commnad. Truncate commnd we can not rollback and delete command we can rolback. truncate commnad is fast compare to delete commnad. truncate commnad release space.

More Questions Like This

Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.