Truncate Vs Delete

Gurus,

This is the common question for the freshers when they require to remove data from tables. They don't know which query can be followed...

Really, this is an interesting question to differentiate between these two commands. Logically speaking, since DELETE logs the data for each row affected by query, it will be slower than TRUNCATE query. TRUNCATE actually deallocates data pages and removes pointers to indexes. Because of this, transaction will be faster than DELETE query.

We need to keep in mind the following restrictions while using TRUNCATE command in our query.

• The user should be db_owner, table owner or ddl_admin to execute TRUNCATE query.
• This will not work for the tables, which are referenced by FOREIGN KEY constraints.

Hope, this post helps you to understand a bit of the difference between these two commands.

Happy coding....
-Harikaran.S