When to use Truncate and Delete Command in SQL

By -
INTRODUCTION
Truncate and Delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both Sql commands can create lot of trouble until you are familiar with details before using it.
An Incorrect choice of command can result is either very slow process or can even blew up log segment, if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use truncate and delete command in SQL but before using these you should be aware of the Differences between Truncate and Delete, and based upon them, we should be able to find out when DELETE is better option for removing data or TRUNCATE should be used to purge tables.

 I have still seen people firing delete command just to empty a table with millions of records which eventually lock the whole table for doing anything and take ages to complete or Simply blew log segment or hang the machine.

 Truncate
Use truncate table if you need to delete all rows, since truncate doesn't allow you to specify WHERE clause. truncate removes data by deallocating space used by table which removes lot of overhead in terms of logging and locking and that's why truncate is faster than delete.

 Caution
What you need to take care is rollback, data deleted by truncate can not be rolled back until data server specifically supports it e.g. MSSQL Server which allows to commit or rollback truncate table statement transnational.
Delete
Delete is another Sql command available for removing records from table. Delete is even more flexible than truncate like it provides support to WHERE Clause which can be use to remove selective data. It logs each row which allows operation to be rolled back and it also fires triggers.

 Disadvantage
One disadvantage of using delete is speed and locking. Delete acquires lock on table and its also very slow operation because of logging, which makes it unsuitable for removing records from large tables. One workaround for this is batch-delete in which you remove batch of records instead on one record at a time. Delete is most suitable fore removing selective data and use it where you want to see log file in database. It’s not useful to purge large amount of data from tables and should not be used, otherwise it could lock the table for very long time, blew log segment and can take ages to complete.

 Precaution While using Truncate and Delete
  • Always take backup of the database before using these commands.
  • As much as possible use BEGIN TRAN before these commands so that you can rollback easily. After proper testing only commit the changes.
  • Use these command in lower life cycle environments first then after proper testing make any changes in production.

Comments

Post a Comment

Popular posts from this blog

Enable HTTP Transport Security (HSTS) in IIS 7

By -
Q.  What is the best way to turn on   HTTP Strict Transport Security   on an IIS 7 web server? Ans:  This can be done by adding following block in Web.Config:               <system.webServer>                     <httpProtocol>                          <customHeaders>                               <add name ="X-CustomName" value="MyCustomValue"/>                                               </customHeaders>                    </httpProtocol>             </system.webServer> We have to configure on IIS that has the ability to custom headers to response: Go to Internet Information Services(IIS) Manager. Configure Response headers that are added to response from the server. Now add your custom header Name and custom Value (Custom header name and value should be same as that in Web.Config).
Read more

Extension Class in C#

By -
Extension method is a new feature in C# 3.0.  Extension methods allow existing classes to be extended without relying on inheritance or having to change the class's source code. This means that if you want to add some methods into the existing String class you can do it quite easily. Here's a couple of rules to consider when deciding on whether or not to use extension methods:   Extension methods cannot be used to override existing methods. An extension method with the same name and signature as an instance method will not be called.     The concept of extension methods cannot be applied to fields, properties or events. Use extension methods sparingly....overuse can be a bad thing!
Read more

Boxing & Unboxing in C#

By -
Boxing is an implicit conversion of a value type to the type object.                       int i = 123;  // A value type                       object box = i; // Boxing Unboxing is an explicit conversion from the type object to a variable type.                       int i = 123; // A value type                       object box = i; // Boxing                       int j = (int)box; // Unboxing Casting: casting is the process of converting a variable from one type to another( from a string to an integer)  In a simple language we can define: Boxing -  converting   value type to reference type  Unboxing - converting reference type to value type. Hope this will make you understand Boxing and Unboxing in C#.
Read more