Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

Difference between Truncate and Delete Expand / Collapse
Author
Message
Posted Thursday, July 7, 2011 12:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:34 AM
Points: 3,261, Visits: 1,955
Nice introduction to delete and truncate statements. Glad you mentioned the difference in how identity values are not reseeded with a delete command as that is definitely an important aspect to consider. That bit me once when I was starting out and I had to go back and fix the identity values manually.
Post #1138383
Posted Thursday, July 7, 2011 8:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:34 AM
Points: 897, Visits: 1,486
pnewhart (7/7/2011)
virat_108 (4/14/2011)
You will get better and easy explanation over here:
http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html


That blog post states that delete cannot be rolled back. This is inaccurate. Both truncate and delete can be rolled back if they are within a begin tran/commit/rollback statement.


That blog post also states that DELETE doesn't "activate triggers".
Careful with what you read because there's a lot of misinformation on the internet. :)

Best regards,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1138633
Posted Friday, July 8, 2011 2:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 4, 2011 5:38 AM
Points: 1, Visits: 3
Hey Prashnat,

This is a beatiful article which you have poasted and very useful.
Post #1138716
Posted Friday, July 8, 2011 2:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:10 AM
Points: 1,155, Visits: 4,624
JustStarted (7/6/2011)
Good article. I knew that Truncate is minimal logged operation but didn't knew that it logs deallocation of data pages.

I have one question though. I know Delete is row by row operations. What happens if we delete rows in bulk? How SQL Sever logs this operation? If we do bulk delete, is it possible to rollback if it is not wrapped in transaction?

Thanks..!


What do you mean by bulk delete? there is no 'bulk delete' such a command in sql.

Delete will write every rows in log file.

If you didn't start within the transaction then you can't rollback because SQL server by default auto commit the transaction.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1138728
Posted Friday, July 8, 2011 9:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, March 3, 2014 11:33 AM
Points: 629, Visits: 130
Muthukkumaran Kaliyamoorthy, please refer to this post for your answer. I happen to post it in different window instead of replying to your post


True there is no such command called "Bulk Delete" in SQL. But you can wrapped DELETE statement in transaction to delete records from your table. Deleting is bulk is faster than just issuing DELETE command. Here's the code snippet. It deletes records in batch of 100000 rows at a time.

BEGIN TRANSACTION
SET NOCOUNT ON;

DECLARE @totalRow int;
DECLARE @intRowCount int;
DECLARE @intErrNo int;

SELECT @totalRow = COUNT(*) FROM [Table] WITH (NOLOCK)
WHERE [Condition] OPTION(MAXDOP 2);

SELECT @intRowCount = @totalRow,
@intErrNo = 0;

WHILE @intRowCount > 0 AND @intErrNo = 0
BEGIN
SET ROWCOUNT 100000;

DELETE FROM [Table]
WHERE [Condition] OPTION(MAXDOP 2);
SELECT @intErrNo = @@ERROR
, @intRowCount = @@ROWCOUNT;
END;

SET ROWCOUNT 0; -- Reset batch size to "all"
END;
Post #1138999
Posted Wednesday, February 1, 2012 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 8:24 PM
Points: 4, Visits: 6
HI this was nice post
for further more simple practical example visit


[url=http://sqldebate.blogspot.in/2012/01/difference-between-truncate-and-delete.html][/url]
Post #1244945
Posted Wednesday, February 1, 2012 4:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 8:24 PM
Points: 4, Visits: 6
Hi for more practical explanation and conceptual funda visit
http://sqldebate.blogspot.in/2012/01/difference-between-truncate-and-delete.html

Post #1244960
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse