Difference between TRUNCATE and DELETE commands in SQL-Server

  • TRUNCATE is a DDL command whereas DELETE is a DML command.
  • TRUNCATE is much faster than DELETE.
  • When you type DELETE. All the data get copied into the Rollback Tablespace first. Then delete operation get performed. That’s why when you type ROLLBACK after deleting a table; you can get back the data (The system gets it for you from the Rollback Tablespace). All this process takes time.
  • But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That’s why TRUNCATE is faster. Once you truncate you can’t get back the data.
  • You can’t rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently.
  • In case of TRUNCATE, Trigger doesn’t get fired. But in DML commands like DELETE Trigger get fired.
  • You can’t use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause.

Leave a Comment