Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Truncate before table drop Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 3:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 2:44 AM
Points: 769, Visits: 856
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



Post #1566661
Posted Thursday, May 1, 2014 5:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 3, 2014 6:29 AM
Points: 185, Visits: 176
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)
Post #1566677
Posted Thursday, May 1, 2014 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 13,320, Visits: 12,804
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566708
Posted Thursday, May 1, 2014 7:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
Doing that is pointless. Truncate deallocates all pages, Drop deallocates all pages and removes the metadata


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1566711
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse