Want create site? Find Free WordPress Themes and plugins.

While working with the database we often need to delete data, sometimes to create more space, sometimes just remove all data to make table ready for next day’s transaction or sometimes just selectively remove stale data. SQL provides a couple of handy commands to remove data e.g. truncate, delete and drop. The last one is bit different because instead of removing data it just deletes table. What is the difference between truncate and delete command in SQL or when to use truncate vs delete is one of the most frequently asked SQL interview question?

In this article, we will first see few differences between them, and based upon them, you should be able to find out when DELETE is a better option for removing data or TRUNCATE should be used to purge tables.

TRUNCATE vs DELETE in SQL:

TRUNCATE:

  • It is a DDL command
  • It does not support WHERE clause/condition
  • Removes all the data all the time
  • Faster than DELETE as it locks entire table
  • It removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log
  • It does not activate triggers
  • Table identity column is reset to seed value
  • Syntax: TRUNCATE TABLE TableName

DELETE:

  • It is a DML command
  • It supports WHERE clause/condition
  • Removes data based on conditions specified in the WHERE clause (removes all the data if there is no WHERE clause)
  • Slower than TRUNCATE as it takes row level locks
  • It removes rows one at a time and records an entry in the transaction log for each deleted row
  • It does activate triggers
  • Table identity column is not reset
  • Syntax: DELETE FROM TableName
    WHERE ColName = ‘YourCondition’
Did you find apk for android? You can find new Free Android Games and apps.

Leave a Comment