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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s