Is Truncate before Drop necessary?

[JohnDTampaBay]

I was recently told that I should truncate a table before I drop it. The thought was dropping a table logs data being removed from the table but truncating doesn't.

This doesn't sound right to me. I thought that in both cases only the deallocation of pages is logged. I can't find anything that covers this. Can someone point me to an article or whatever that explains this?


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JamesLean]

That's certainly not something that I've ever heard/seen.

--James

 

[JohnDTampaBay]

In ten years of SQL work, I have never heard it either. I'd just like a little "proof" for my co-worker.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[mkal]

From BOL
Deleting All Rows Using TRUNCATE TABLE
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all rows in a table. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs only the deallocation of whole data pages. TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.

As with DELETE, the definition of a table emptied using TRUNCATE TABLE remains in the database, along with its indexes and other associated objects. The DROP TABLE statement must be used to drop the definition of the table.

 

[jbenson001]

Mkal, the question is, if you use a Drop Table, do the rows get logged as if a delete is used.

 

[Catadmin]

John,

One way to test it is to get one of the Transaction Log mining tools (Apex's trial version of the log reader will allow you to connect to a local DB that isn't Pubs/Northwind/Adventure works) and test the theory on a lab machine.

Set up a Trans Log backup to occur every 5 minutes, then delete your table without a Truncate statement. Use the trial version of the log reader to go into the log and see if the rows are listed there or not.

That should resolve the question.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???