Difference Between Delete & Truncate in Sql Server

By -
INTRODUCTION
While working on database, we are using Delete and Truncate without knowing the differences between them and when to use them. In this article we will discuss the difference between Delete and Truncate in Sql.

 Delete
  • Delete is a DML command.
  • Delete statement is executed using a row lock,each row in the table is locked for deletion.
  • We can specify filters in where clause.
  • It deletes specified data if where condition exists.
  • Delete activities a trigger because the operation are logged individually.
  • Slower than Truncate because it Keeps logs
Truncate
  • Truncate is a DDL command.
  • Truncate table always lock the table and page but not each row.As it removes all the data.
  • Cannot use Where condition. 
  • It Removes all the data.
  • Truncate table cannot activate a trigger because the operation does not log individual row deletions.
  • Faster in performance wise, because it doesn't keep any logs.
Note
Delete and Truncate both can be rolled back when used with Transaction.
If Transaction is done, means committed then we can not rollback Truncate command from log files, but we can still rollback Delete command from Log files, as delete write records them in Log file in case it is needed to rollback in future from log files.

If you have a Foreign key constraint referring to the table you are trying to truncate, this won't work even if the referring table has no data in it. This is because the foreign key checking is done with DDL rather than DML. This can be got around by temporarily disabling the foreign key constraint(s) to the table.

Delete table is a logged operation. So the deletion of each row gets logged in the transaction log, which makes it slow.
Truncate table also deletes all the rows in a table, but it won't log the deletion of each row instead it logs the deallocation of the data pages of the table, which makes it faster.


 ~ If accidentally you removed all the data from table using Delete/Truncate. You can rollback committed transaction. Restore the last backup and run transaction log till the time when Delete/Truncate is about to happen.

 Check this Article Rollback Table after Truncate Command in Sql or
Let me know if I missed any and I'll add them. 

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

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
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