You are hereBlogs / shantanu's blog / Difference between Delete and Truncate

Difference between Delete and Truncate


By shantanu - Posted on 19 February 2011

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.

 

 Please go through this URL for more points

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