The Difference between Truncate and Delete in SQL Server

Question

The Difference between Truncate and Delete in SQL Server

Viewing 15 posts - 31 through 45 (of 66 total)

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

MudLuck SSCrazy Points: 2301 More actions · Answer 1

All,

This part of the article below has me the tiniest bit flustered as I believe it can take newbie’s down the wrong road.

Author-- "As TRUNCATE resets the identity column counter, in the case where you want to retain the identity counter, use DELETE instead of TRUNCATE."

I don't think people should be basing their choice on which method to use based solely on the identity counter being reset. I mean if you have a multiple gigabit table and utilize a delete with no where clause statement. You have a situation where delete will take forever (mins- hours) and if this is prod most likely the effected extents will be shared by other tables (yes it happens) so now you have a scenario where your exclusive locks could be slowing down other tables access to those extents as they are being updated . On top of that the CPU will also get pegged as the lock manager enforces concurrency on the deletes, so performance there will also suffer. Overall a poor choice in my opinion.

All of this when you could simply truncate the table then issue the statement below to reset the identity.

--Like most good things this example was taken from BOL

USE AdventureWorks;

GO

DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30)

GO

I mean if your supper nervous wrap the two commands in a transaction and check for failure on truncate with @@ERRROR and enforce rollback if needed wrap in a SPROC and you'll become a SQL Hero!

The rest of the article was stuff most of us I think have learned from experience I did like the detail on the "minimal logging" of truncate I have inferred that has always been the process (deallocate from datafile but not erase) but that is the first time I have seen someone put it in print. Thanks author!

MudLuck

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 2

Filip Poverud (11/26/2007)
I rated average because:
1 - You have a type mismatch in your desription of your statements regarding the table name.
2 - You use the words remove / delete inconsistenly when you explain the difference.
---
Other that that, the last part about the locking and constraints is very good.

Hi Filip,

Can u please explain me this more clearly, "type mismatch "??

and

if u rated it average only with the matter that i have used delete and remove.. then i don't think its a big matter of concern, else its your choice...

and thanks for your appreciation about the locking and constraints.

Thank you:)

Thanks,
Prashant

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 3

MudLuck (11/26/2007)
All,
This part of the article below has me the tiniest bit flustered as I believe it can take newbie’s down the wrong road.
Author-- "As TRUNCATE resets the identity column counter, in the case where you want to retain the identity counter, use DELETE instead of TRUNCATE."
I don't think people should be basing their choice on which method to use based solely on the identity counter being reset. I mean if you have a multiple gigabit table and utilize a delete with no where clause statement. You have a situation where delete will take forever (mins- hours) and if this is prod most likely the effected extents will be shared by other tables (yes it happens) so now you have a scenario where your exclusive locks could be slowing down other tables access to those extents as they are being updated . On top of that the CPU will also get pegged as the lock manager enforces concurrency on the deletes, so performance there will also suffer. Overall a poor choice in my opinion.
All of this when you could simply truncate the table then issue the statement below to reset the identity.
--Like most good things this example was taken from BOL
USE AdventureWorks;
GO
DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30)
GO
I mean if your supper nervous wrap the two commands in a transaction and check for failure on truncate with @@ERRROR and enforce rollback if needed wrap in a SPROC and you'll become a SQL Hero!
The rest of the article was stuff most of us I think have learned from experience I did like the detail on the "minimal logging" of truncate I have inferred that has always been the process (deallocate from datafile but not erase) but that is the first time I have seen someone put it in print. Thanks author!
MudLuck

Hi MudLuck,

Thanks for your suggestion, its really good but from teh advanced point of view, a beginer should have the knoeledge first that wht the Truncate and Delete is??? and as you said the following,

The rest of the article was stuff most of us I think have learned from experience

So i think when a beginer will know about truncate and delet tehn with the expeirence he/she'll find this thing by their own, and about the experinced programers like you you already know this...:)

But your point is really good, and i think you'll also agree with my this point that NONBODY is perfect, I put my best effort to make this article good, I am also not an expert of sql, I am also at the begining stage, so from that point of view i think i have done well. But through the comments on this article i have learned many things,

Thanks,

Thanks,
Prashant

Nimesh T Mathews Right there with Babe Points: 727 More actions · Answer 4

Good Article.

One of my doubt is still unanswered about truncate

Will you be able to recover data of a table after truncating it ?

Regards

Nimesh

Carl Ashby SSC Journeyman Points: 88 More actions · Answer 5

This is an excellent article. I hope there is more where this came from.

Ian Crandell SSCommitted Points: 1543 More actions · Answer 6

Thanks for an informative article. Also, thanks to all who provided insight to the information.

Ian.

"If you are going through hell, keep going."
-- Winston Churchill

Ceteras Vasile Grasshopper Points: 21 More actions · Answer 7

great article!

however,

In case of TRUNCATE and DELETE, because they are deleting data from the table, the DELETE TRIGGER will fire if present and also ...

and then:

* TRUNCATE - When we run the TRUNCATE command to delete all the rows of a table it actually doesn't remove any row rather it deallocates the data pages. So in the case of the TRUNCATE command, triggers will not be fired because here no modification takes place.

ok, I've got it, but it seems to me that the first quote contains a logical contradiction...

my 2 cents

thank you

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 8

Hi,

Actually the line which is below:

In case of TRUNCATE and DELETE, because they are deleting data from the table, the DELETE TRIGGER will fire if present and also the INSTEAD OF and AFTER triggers can be triggered if present. The INSERT and UPDATE triggers will not be fired here.

Actually above lines meaning that the delete trigger will fire caz the both statements perform deletion on the table and update and insert will not fire caz deletion is happening there, But what the actual effect of both these statements take a look in the detail,

Actually a beginer always thinks that caz both the statements deleting teh data so that means delete trigger will fire, so the 1st line u xcan assume as that it will happen but wht actually happening is given in the below lines taht are as follows written in teh article.

So let's take both one by one:
* TRUNCATE - When we run the TRUNCATE command to delete all the rows of a table it actually doesn't remove any row rather it deallocates the data pages. So in the case of the TRUNCATE command, triggers will not be fired because here no modification takes place. As we know that TRUNCATE is a DDL command, and DDL commands doesn't modify your data; instead they modify your table structure and definition.
* DELETE - In case of DELETE the DELETE trigger will be fired if present and also if the INSTEAD OF and AFTER triggers for DELETE TRIGGER is present then they also will be fired. As we know that delete command is a DML command and it deletes the data on row-by-row basis. So that means delete is modifying the data by deleting it from the table. As we know delete is a DML command and trigger will be fired whenever any DML operation on the table takes place.

Thanks,

Thanks,
Prashant

Mike Perkins Grasshopper Points: 22 More actions · Answer 9

Hi Prashant,

"Cascade" refers to an implicit action, such as cascade delete or cascade update. In the case of cascade delete, say you have a table Album with an AlbumGuid as primary key and another table AlbumTrack with a column AlbumGuid that points to a Album row. If you have the key connecting these set to cascade delete, then when the Album row is deleted, all AlbumTrack rows dependent on it will also be deleted.

Hope that helps,

Mike

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 10

Mike Perkins (11/28/2007)
Hi Prashant,
"Cascade" refers to an implicit action, such as cascade delete or cascade update. In the case of cascade delete, say you have a table Album with an AlbumGuid as primary key and another table AlbumTrack with a column AlbumGuid that points to a Album row. If you have the key connecting these set to cascade delete, then when the Album row is deleted, all AlbumTrack rows dependent on it will also be deleted.
Hope that helps,
Mike

Ya that means whenever you'll having a table related with other table with a firgn key relation and on that table you want to delete any data then the action called as Cascade Delete OR if we are inserting or updating that table then that means cascade Insert and Cascade update,

right???

Thanks..

Thanks,
Prashant

Dennis Wilson Grasshopper Points: 13 More actions · Answer 11

I agree that the article was good and could be made even more complete with the addition of a short discussion about being unable to use the TRUNCATE TABLE statement against a table that is related to another table using Declarative Referential Integrity (DRI).

In this case, DELETE must be used in order to maintain the referential integrity, especially if the table being truncated contains primary key values used in another table.

Thanks,

Dennis

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 12

Dennis Wilson (12/1/2007)
I agree that the article was good and could be made even more complete with the addition of a short discussion about being unable to use the TRUNCATE TABLE statement against a table that is related to another table using Declarative Referential Integrity (DRI).
In this case, DELETE must be used in order to maintain the referential integrity, especially if the table being truncated contains primary key values used in another table.
Thanks,
Dennis

Sure Dennis,

This is the good point to include in the article,

Thanks:)

Thanks,
Prashant

chithrasooriyen Newbie Points: 7 More actions · Answer 13

Anyone, please clarify whether performing a COMMIT operation after DELETE is same as TRUNCATE operation?

Dan Heile SSC Veteran Points: 249 More actions · Answer 14

Thanks for your article. It really helped to clear up some of the differences. I do have a question on space consumption. we had a delete from instead of a truncate on a stage data table by mistake. when looking at the space usage report by table, this stage table showed 2gig used for only 500k rows of 300 bytes each (150m or so).

also, the time to access the stage table was excessive as well. after truncating and reloading, it was back to 150meg.

why would a year of weekly DELETE FROM commands where 500k rows were deleted cause the space to be attributed to the stage table and cause the query times to run so high?

I could see it being marked as needed for shrinking but dont pages that have had all their rows deleted, be avail for new rows?

thanks again!

Dan <><

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 15

Space might be available for new rows, but they aren't necessarily reused. Your clustered index might cause things to be more spread out, and depending on the values, pages might split.