Difference between Delete and Truncate

Removing records from a table is a crucial operation. So I would like to pen down my knowledge about removing records from a table. There are two ways to do the same, Truncate and Delete.

I would like to explain the basic difference between two.

1. Delete is a DML whereas truncate is a DDL. Truncate deallocates the data pages for the table and does'nt delete them row by row. So if there is an Identity column in the table it would be reset to initial seed after truncate.

2. For Delete a Transaction Log is maintained. For this simple reason a Delete statement can be RolledBack and recovered using the transaction log.

On the other hand, for Truncate there is no entry in transaction log. So a truncate statement can't be recovered once committed. But we can always rollback a truncate statement if we trap it inside a TRANSACTION scope.

3. Where Clause can’t be used in truncate, so a truncate statement can't LOCK Rows.

                Syntax of both operations is as below
                                DELETE * FROM TableName WHERE Clause
                                TRUNCATE TableName
 
4. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

 

Anonymous's picture

 Please go through this URL

 Please go through this URL for more points

Anonymous's picture

Nice explanation.

Really awesome post and you have explained everything very clearly about Delete and truncate.

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr><br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong><font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <embed> <object> <param> <strike> <caption>
  • You can enable syntax highlighting of source code with the following tags: <c>, <cpp>, <csharp>, <drupal5>, <drupal6>, <java>, <javascript>, <mysql>, <php>, <python>, <ruby>, <sql>, <tsql>, <vbnet>, <xml>. Beside the tag style "<foo>" it is also possible to use "[foo]". PHP source code can also be enclosed in <?php ... ?> or <% ... %>.
  • Lines and paragraphs break automatically.

More information about formatting options

Who's online

There are currently 0 users and 3 guests online.

Recent Answers

Anonymous added a answer to LC3 Assembly Palindrome Program (3 years 23 weeks ago)
 
Anonymous added a answer to LC3 Assembly Palindrome Program (3 years 23 weeks ago)
 
Anonymous added a answer to Error-GetTypeHashCode()': no suitable method found to override (3 years 40 weeks ago)
 
Anonymous added a answer to AutoIncrement vs UniqueIdentifier (4 years 13 weeks ago)
 
vaibhav added a answer to Linq query on Datatable (4 years 15 weeks ago)
 
vaibhav added a answer to Delete Duplicate Records From a table (4 years 27 weeks ago)