The Difference between Truncate and Delete in SQL Server

Question

Post reply

The Difference between Truncate and Delete in SQL Server

Viewing 15 posts - 46 through 60 (of 66 total)

You must be logged in to reply to this topic. Login to reply

virat_108 SSC Enthusiast Points: 116 More actions · Answer 1

You will get better and easy explanation over here:

http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html

virat_108 SSC Enthusiast Points: 116 More actions · Answer 2

You will get better and easy explanation over here:

http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html

seven.srikanth Ten Centuries Points: 1212 More actions · Answer 3

seven.srikanth

Ten Centuries

Points: 1212

April 15, 2011 at 10:05 pm

#1312403

Wonderful Article..... I rate it 100/100....

SREERAJ. R Old Hand Points: 303 More actions · Answer 4

Hi Prasanth,

Fantastic article. Hats off to you. It's really comprehensive and provides useful information. Thank you very much for writing such a good article.

Thanks,

Sreeraj

Swami Polnati Newbie Points: 7 More actions · Answer 5

This post really helps and give much more details. Thanks Prashant. Keep doing good job.

smanage 666 Grasshopper Points: 17 More actions · Answer 6

QUESTION

Is "TRUNCATE" and "ROLLBACK" reliable?

If TRUNCATE de-allocates the pages, which can be overwritten by subsequent operations, can the ROLLBACK of a TRUNCATE operation restore the original data?

Eg.

BEGIN TRANSACTION

..

TRUNCATE table1

..

insert to other tables, which may overwrite the pages

(meanwhile, other operations outside this transaction could also re-use the de-allocated pages)

....

ROLLBACK

Is table1 restored to its original state?

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 7

Dear Prashant Pandey,

Nice explanation of Difference, I learned definitely something new from this post.

deepalijawalkar Grasshopper Points: 11 More actions · Answer 8

Hi..

Your article is good.But just want to know briefly about deallocation of data pages..?

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 9

Hi.

Thank you for the long explanations in the article. Much appreciated. A lot of information in a single post.

Two notes I'd like to make to help improve the article:

1) You state that TRUNCATE doesn't do any structure or table modification at the begining of the article and then you later state that "... the TRUNCATE is modifying the table definition or structure, which comes under the DDL operations".

2) Try to refrain from using "I think" in technical articles.

Overall, a great article. 🙂

Keep up the good work. This sort of information is fundamental.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

mohammed.ahmed81 Grasshopper Points: 12 More actions · Answer 10

Excellent read. very simple and Bite size..just how i like to read.

keep up the good work 🙂

alex.khan Newbie Points: 5 More actions · Answer 11

Good article. Missing info on how statistics are affected in each case.

scott mcnitt Ten Centuries Points: 1028 More actions · Answer 12

I know when I worked with Oracle, a truncate would perform an implicit commit on any trancaction the truncate statement was riding on.

That may come as a BIG suprise when put into production!

charley.cross Grasshopper Points: 15 More actions · Answer 13

Okay, I admit this is a trivia question from a wordsmith, but how did it come to pass that they use the word "truncate" in SQL to mean "nuke all the records from the table"? It seems a misuse of the English word. Maybe the person who invented SQL wasn't a skilled writer. "Truncate" means "to cut short" or "to remove a portion". It doesn't mean "to remove all". It seems something like "purge" might have been a better choice of verb.

Too late now, huh?

🙂

sbamaty SSC Eights! Points: 936 More actions · Answer 14

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

pnewhart SSC Eights! Points: 983 More actions · Answer 15

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.