.NET Zone
.NET Zone is brought to you in partnership with:

Senthil Kumar is a Software Engineer who has around 3 years of experience in IT industry. He is currently working as a Software Engineer in Bangalore and works mainly on the Windows or Client Development technologies and has good working experience in C#/.NET, Delphi, Winforms and SQL Server. He is also a Microsoft Technology Certified Professional in ASP.NET. He Blogs at http://www.ginktage.com and http://www.windowsphonerocks.com. He enjoys learning as much as he can about all things related to technologies to get a well-rounded exposure of technologies that surround him. Senthil completed his Master of Computer Applications from Christ College (Autonomous), Bangalore in the year 2009 and is a MCA Rank Holder. He has passion for Microsoft technologies especially Windows Phone development. You can connect with him on Twitter at (http://twitter.com/isenthil) , on Facebook at (http://www.facebook.com/kumarbsenthil) and his blog (www.ginktage.com). Senthil is a DZone MVB and is not an employee of DZone and has posted 118 posts at DZone. You can read more from them at their website. View Full User Profile

Difference between DELETE and TRUNCATE command in SQL Server

07.04.2012
| 1475 views |
  • submit to reddit

Below are few points that differentiate the DELETE and TRUNCATE command.

Sl. No.

DELETE

TRUNCATE
1 This removes the rows one at a time and then records it in the transaction log for each of those deleted row. This used fewer transaction log than delete and is faster
2 This can be used with or without a Where clause This removes all rows from a table
3 Can be rolled back. Cannot be rolled back
4 It is a DML Command. It is a DDL Command.
5 When an Identity is used , it is not reset Identity is reset

 

Published at DZone with permission of Senthil Kumar, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Richard Carr replied on Thu, 2012/07/05 - 4:57pm

Hi Senthil,

there's an error in your table of differences. It's a common misconception about TRUNCATE that it cannot be rolled back. This is not the case, you can roll back a TRUNCATE in SQL Server if you execute the truncation within a transaction and rollback that transaction in the same session.

What you cannot do is guarantee the ability to rollback a TRUNCATE from the log files when the database is full recovery mode.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
"Starting from scratch" is seductive but disease ridden
-Pithy Advice for Programmers