May 1, 2014 at 3:36 am
Hi all,
I have come across some code that does this:
Truncate table Tablename
Drop table Tablename
I have never done a truncate before a drop. I have looked online for reasoning behind this (some people have suggested its quicker, but i have yet to see this proved), but most posts seem to break down into arguments over saying truncate isn't logged (it is!).
So has anyone found if this is actually quicker?
Dan
May 1, 2014 at 5:33 am
I believe the performance gain with truncate before drop is minimal to no gain. This is what I could practically see while deleting large table!
-Vijred (http://vijredblog.wordpress.com)
May 1, 2014 at 7:34 am
I would think that it is actually slower. Both truncate and drop will deallocate pages. These page deallocations are what is logged in a truncate and a drop. This basically means we have two implicit transactions instead of one. The first to deallocate all the pages and a second to drop the table. It would be interesting to see some stats or references where people claim that truncating and then dropping is faster.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2014 at 7:36 am
Doing that is pointless. Truncate deallocates all pages, Drop deallocates all pages and removes the metadata
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply