Difference between Delete and Truncate

  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • strict warning: Declaration of views_plugin_display::options_validate() should be compatible with views_plugin::options_validate(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_display_page::options_submit() should be compatible with views_plugin_display::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_display_block::options_submit() should be compatible with views_plugin_display::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_display_attachment::options_submit() should be compatible with views_plugin_display::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_field_broken::ui_name() should be compatible with views_handler::ui_name($short = false) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_sort_broken::ui_name() should be compatible with views_handler::ui_name($short = false) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter::options_validate() should be compatible with views_handler::options_validate($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter::options_submit() should be compatible with views_handler::options_submit($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_broken::ui_name() should be compatible with views_handler::ui_name($short = false) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_user_name::value_submit() should be compatible with views_handler_filter_in_operator::value_submit($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_user_name::value_validate() should be compatible with views_handler_filter::value_validate($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • strict warning: Declaration of views_handler_field_comment_username::init() should be compatible with views_handler_field::init(&$view, $options) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_handler_filter_boolean_operator::value_validate() should be compatible with views_handler_filter::value_validate($form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_style_default::options() should be compatible with views_object::options() in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_row::options_validate() should be compatible with views_plugin::options_validate(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • strict warning: Declaration of views_plugin_row::options_submit() should be compatible with views_plugin::options_submit(&$form, &$form_state) in /home/content/02/6751802/html/techpint/sites/all/modules/views/includes/handlers.inc on line 77.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • strict warning: Non-static method view::load() should not be called statically in /home/content/02/6751802/html/techpint/sites/all/modules/views/views.module on line 843.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.
  • warning: Creating default object from empty value in /home/content/02/6751802/html/techpint/sites/all/modules/views/modules/comment/views_handler_field_comment_username.inc on line 33.

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 8 guests online.

Recent Answers

Anonymous added a answer to 301 Redirect in asp.net (17 weeks 2 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 2 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 3 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 4 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 4 days ago)
 
Anonymous added a answer to 301 Redirect in asp.net (17 weeks 5 days ago)