Delete Vs Truncate

Question

Post reply

Delete Vs Truncate

sunny.tjk

SSCertifiable

Points: 5041
More actions
May 25, 2010 at 9:06 am

#140511

I read an article on the difference between delete and truncate operation.
(1) It said "DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted."
What does the author mean by "each row gets logged and physically deleted"?
(2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".
Does he mean that, a row in a table can be deleted even when a foreign key exists?
(3) He also says that "TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. ". What does he mean by "deallocation of the data pages in which the data exists"?
I'd really appreciate if someone could explain this in more detail.
Thanks everyone.

Viewing 15 posts - 1 through 15 (of 18 total)

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

Perry Whittle SSC Guru Points: 233792 More actions · Answer 1

Truncate will also reset seed values where delete won't

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Leo.Miller SSChampion Points: 13035 More actions · Answer 2

I recommend you do some basic SQL courses. Some one may have time to answer these sorts of questions, but if you read the SQL BOL (books on line) or do a course you will get answers to these. The forum is really about solving peoples problems, not giving lessons.

Leo

There are 10 types of people in the world.

Those who understand binary and and those that don't

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

sunny.tjk (5/25/2010)
I read an article on the difference between delete and truncate operation.
(1) It said "DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted."
What does the author mean by "each row gets logged and physically deleted"?
(2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".
Does he mean that, a row in a table can be deleted even when a foreign key exists?
(3) He also says that "TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. ". What does he mean by "deallocation of the data pages in which the data exists"?
I'd really appreciate if someone could explain this in more detail.
Thanks everyone.

I agree with Leo... you need to hit the books a bit especially Books Online. If you don't know what that is, please post back. You should probably learn a bit about rows, pages, and extents.

For your questions, consider how a book is made. For a DELETE, you would be ripping one sentence out of each page of the book at a time and writing down that you ripped out a sentence. For TRUNCATE, you would just throw out the whole book and write down that you threw out the book.

Guess which would be faster to do?

Heh... and if you have a "chain" on the book in the form of a foreign key, you won't be able to throw the whole book out, but you can still rip sentences out of it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

VastSQL SSC-Dedicated Points: 35970 More actions · Answer 4

VastSQL

SSC-Dedicated

Points: 35970

May 26, 2010 at 1:17 am

#1172498

check my blog

http://rknairblog.blogspot.com/

Perry Whittle SSC Guru Points: 233792 More actions · Answer 5

I think Jeff's expo pretty much explains it in laymans terms as well as you possibly can!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 6

sunny.tjk

SSCertifiable

Points: 5041

May 26, 2010 at 12:21 pm

#1172944

Jeff, you made it look so simple. Thanks a lot...

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

sunny.tjk (5/26/2010)
Jeff, you made it look so simple. Thanks a lot...

Heh... thanks for the feedback, sunny. In truth, it's the only way I can understand this stuff myself. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

denis.hosdil SSC Veteran Points: 253 More actions · Answer 8

(2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".
Does he mean that, a row in a table can be deleted even when a foreign key exists?

Ex: Say Items.OrderID is an FK to Orders.OrderID. You can delete a row from the Orders table if it does not have any corresponding Items.

nabidavid SSC Eights! Points: 815 More actions · Answer 9

Jeff Moden (5/25/2010)
sunny.tjk (5/25/2010)
I read an article on the difference between delete and truncate operation.
(1) It said "DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted."
What does the author mean by "each row gets logged and physically deleted"?
(2)He also says that "You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.".
Does he mean that, a row in a table can be deleted even when a foreign key exists?
(3) He also says that "TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. ". What does he mean by "deallocation of the data pages in which the data exists"?
I'd really appreciate if someone could explain this in more detail.
Thanks everyone.
I agree with Leo... you need to hit the books a bit especially Books Online. If you don't know what that is, please post back. You should probably learn a bit about rows, pages, and extents.
For your questions, consider how a book is made. For a DELETE, you would be ripping one sentence out of each page of the book at a time and writing down that you ripped out a sentence. For TRUNCATE, you would just throw out the whole book and write down that you threw out the book.
Guess which would be faster to do?
Heh... and if you have a "chain" on the book in the form of a foreign key, you won't be able to throw the whole book out, but you can still rip sentences out of it.

Jeff that is the best analogy I have ever heard on this subject. 😉 You deserve a proverbial pat on the back.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Heh... thanks for the feedback. Just don't throw the book at me. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

VastSQL SSC-Dedicated Points: 35970 More actions · Answer 11

Thanks Jeff.That was really nice example and i liked the way u explained simple but really powerful so that noone will forget

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Ratheesh.K.Nair (5/27/2010)
Thanks Jeff.That was really nice example and i liked the way u explained simple but really powerful so that noone will forget

Thanks for the nice compliment, Ratheesh. Heh... like I said, I have to explain it to myself that way so I'm the no one that will forget. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

nitu.guptaji Newbie Points: 6 More actions · Answer 13

mean at the time of deletion log file is maintened we can recover data but in the case of truncation we can not recover because it doesn't maintain log file

Perry Whittle SSC Guru Points: 233792 More actions · Answer 14

Jeff Moden (5/27/2010)
Just don't throw the book at me. 😛

No give him a taste of his own medicine and throw a few pork chops instead 😀 :laugh:

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉