Difference between Truncate and Delete

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prashant Pandey » Difference between Truncate and Delete

67 posts, Page 7 of 7

«««3 4 5 6 7

Difference between Truncate and Delete

Rate Topic

Display Mode

Topic Options

Author

Message

KWymore

Posted Thursday, July 07, 2011 12:04 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 2,574, Visits: 1,532

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

Post #1138383

codebyo

Posted Thursday, July 07, 2011 8:06 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436

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

santu.kondapally

Posted Friday, July 08, 2011 2:09 AM

Forum Newbie

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

Hey Prashnat,

This is a beatiful article which you have poasted and very useful.

Post #1138716

muthukkumaran Kaliyamoorthy

Posted Friday, July 08, 2011 2:49 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 1,123, Visits: 4,423

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

sbamaty

Posted Friday, July 08, 2011 9:39 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 12:13 PM
Points: 628, Visits: 101

Muthukkumaran Kaliyamoorthy, please refer to this post for your answer. I happen to post it in different window instead of replying to your post BigGrin

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

langhanoja.blog

Posted Wednesday, February 01, 2012 3:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 8:43 PM
Points: 4, Visits: 5

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

langhanoja.blog

Posted Wednesday, February 01, 2012 4:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 8:43 PM
Points: 4, Visits: 5

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 »

67 posts, Page 7 of 7

«««3 4 5 6 7

Permissions