InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:28:51 GMT20http://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHi for more practical explanation and conceptual funda visit[url]http://sqldebate.blogspot.in/2012/01/difference-between-truncate-and-delete.html[/url]Wed, 01 Feb 2012 04:08:04 GMTlanghanoja.bloghttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHI 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]Wed, 01 Feb 2012 03:32:17 GMTlanghanoja.bloghttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxMuthukkumaran Kaliyamoorthy, please refer to this post for your answer. I happen to post it in different window instead of replying to your post :-DTrue 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;Fri, 08 Jul 2011 09:39:09 GMTsbamatyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]JustStarted (7/6/2011)[/b][hr]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?[b] If we do bulk delete[/b], is it possible to rollback if it is not wrapped in transaction? Thanks..![/quote]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.Fri, 08 Jul 2011 02:49:03 GMTmuthukkumaran Kaliyamoorthyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHey Prashnat,This is a beatiful article which you have poasted and very useful.Fri, 08 Jul 2011 02:09:09 GMTsantu.kondapallyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]pnewhart (7/7/2011)[/b][hr][quote][b]virat_108 (4/14/2011)[/b][hr]You will get better and easy explanation over here:http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html[/quote]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.[/quote]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,Thu, 07 Jul 2011 20:06:24 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxNice 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. :(Thu, 07 Jul 2011 12:04:04 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]virat_108 (4/14/2011)[/b][hr]You will get better and easy explanation over here:http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html[/quote]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.Thu, 07 Jul 2011 08:01:21 GMTpnewharthttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxGood 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..!Wed, 06 Jul 2011 11:29:05 GMTsbamatyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxOkay, 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?:-)Tue, 05 Jul 2011 09:07:16 GMTcharley.crosshttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxI 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!Mon, 04 Jul 2011 10:49:43 GMTscott mcnitthttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxGood article. Missing info on how statistics are affected in each case.Mon, 04 Jul 2011 10:00:46 GMTalex.khanhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxExcellent read. very simple and Bite size..just how i like to read.keep up the good work :-)Mon, 04 Jul 2011 08:35:39 GMTmohammed.ahmed81http://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHi.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,Mon, 04 Jul 2011 05:22:50 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHi..Your article is good.But just want to know briefly about deallocation of data pages..?Mon, 04 Jul 2011 05:17:42 GMTdeepalijawalkarhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxDear Prashant Pandey,Nice explanation of Difference, I learned definitely something new from this post.Mon, 04 Jul 2011 00:25:49 GMTazhar.iqbal499http://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxQUESTIONIs "TRUNCATE" and "ROLLBACK" reliable?If TRUNCATE de-allocates the pages, [b]which can be overwritten by subsequent operations[/b], 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)....ROLLBACKIs table1 restored to its original state?Sun, 03 Jul 2011 23:15:06 GMTsmanage 666http://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxThis post really helps and give much more details. Thanks Prashant. Keep doing good job.Sun, 03 Jul 2011 22:06:08 GMTSwami Polnatihttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHi 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,SreerajFri, 06 May 2011 05:43:32 GMTSREERAJ. Rhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxWonderful Article..... I rate it 100/100....Fri, 15 Apr 2011 22:05:34 GMTseven.srikanthhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxYou will get better and easy explanation over here:http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.htmlThu, 14 Apr 2011 23:13:07 GMTvirat_108http://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxYou will get better and easy explanation over here:http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.htmlThu, 14 Apr 2011 23:11:10 GMTvirat_108http://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxSpace 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.Fri, 11 Jul 2008 10:31:22 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxThanks 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 <><Fri, 11 Jul 2008 10:22:09 GMTDan Heilehttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxAnyone, please clarify whether performing a COMMIT operation after DELETE is same as TRUNCATE operation?Tue, 13 May 2008 00:45:50 GMTchithrasooriyenhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]Dennis Wilson (12/1/2007)[/b][hr]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[/quote]Sure Dennis,This is the good point to include in the article, Thanks:)Sun, 02 Dec 2007 07:56:12 GMTPrashant Pandeyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxI 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,DennisSat, 01 Dec 2007 15:28:11 GMTDennis Wilsonhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]Mike Perkins (11/28/2007)[/b][hr]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[/quote]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..Wed, 28 Nov 2007 23:26:38 GMTPrashant Pandeyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHi 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,MikeWed, 28 Nov 2007 23:07:18 GMTMike Perkinshttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxHi,Actually the line which is below:[quote]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.[/quote]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.[quote]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.[/quote]Thanks,Wed, 28 Nov 2007 21:46:35 GMTPrashant Pandeyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxgreat article!however, [quote]In case of [b]TRUNCATE and DELETE[/b], because they are deleting data from the table, the DELETE TRIGGER [b]will fire if present[/b] and also ...[/quote]and then:[quote] * 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, [b]triggers will not be fired[/b] because here no modification takes place. [/quote]ok, I've got it, but it seems to me that the first quote contains a logical contradiction... my 2 centsthank youWed, 28 Nov 2007 06:26:29 GMTCeteras Vasilehttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxThanks for an informative article. Also, thanks to all who provided insight to the information.Tue, 27 Nov 2007 08:00:23 GMTIan Crandellhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxThis is an excellent article. I hope there is more where this came from.Tue, 27 Nov 2007 07:18:23 GMTCarl AShbyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxGood Article.One of my doubt is still unanswered about truncateWill you be able to recover data of a table after truncating it ?RegardsNimeshTue, 27 Nov 2007 03:11:21 GMTNimesh T Mathewshttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]MudLuck (11/26/2007)[/b][hr]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 BOLUSE AdventureWorks;GODBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30)GOI 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[/quote]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,[quote]The rest of the article was stuff most of us [b]I think have learned from experience [/b][/quote]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,Mon, 26 Nov 2007 11:44:31 GMTPrashant Pandeyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]Filip Poverud (11/26/2007)[/b][hr]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.[/quote]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:)Mon, 26 Nov 2007 11:27:28 GMTPrashant Pandeyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxAll,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 BOLUSE AdventureWorks;GODBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30)GOI 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!MudLuckMon, 26 Nov 2007 11:18:28 GMTMudLuckhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxI 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.Mon, 26 Nov 2007 07:09:04 GMTFilip Poverud-414266http://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspx[quote][b]Mike Perkins (11/26/2007)[/b][hr]Good article, but it would also be good to explicitly tell the effect when cascade delete is turned on for a relation with another table. My guess would be that truncate doesn't cascade the delete to the dependent table.Mike[/quote]Hi Mike,Can u please explain me that "cascade delete" means when a table is refrenced or connected with other table usinga foriegn key, and when we try to delete data from any table the it means we are performing the cascade delete operation, i am having this doubt caz from last 2-3 days i am reading about triggers and i found this term cascade in many articles. Is cascade means forign key relation???Is it....Mon, 26 Nov 2007 01:17:51 GMTPrashant Pandeyhttp://www.sqlservercentral.com/Forums/Topic424812-1122-1.aspxGood article, but it would also be good to explicitly tell the effect when cascade delete is turned on for a relation with another table. My guess would be that truncate doesn't cascade the delete to the dependent table.MikeMon, 26 Nov 2007 01:10:02 GMTMike Perkins