wodom (10/7/2009)
OK, so from that aspect it looks like DROP is safer (and faster) than TRUNCATE, especially for very large tables. (And being faster makes it safer still.)
They do pretty much the same thing. As far as I know, truncate table deallocates the extents, drop table deallocates the extents and removes the table's metadata. There's no question of safety, both of them will succeed as a whole or fail as a whole (because they are logged operations) and both can be rolled back. On speed, I haven't tested but I'd be surprised if there was a major difference between the two.
Unless you're saying that with a logged operation, it can just do a rollback instead?
Logged operations will always be rolled back/forward upon restart. It's called restart-recovery. SQL reads through the transaction log, finds all transactions that completed but did not have the modified pages written to disk and redoes them, then it finds all the transactions that had started but not committed at the point the server failed and rolls them back.
If you run TRUNCATE within an explicit transaction, it can be rolled back, like any other operation.
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