1

So I have been researching SQL standards with regard to using TRUNCATE vs DROP. My company is trying to determine whether we should use TRUNCATE before a DROP as a standard coding convention, or if we should just use DROP. Typically, we have always used TRUNCATE and DROP instead of just DROP.

According to this previously answered question, it looks like there is no reason to use both: https://dba.stackexchange.com/questions/4163/why-use-both-truncate-and-drop

The above link also addresses that with TRUNCATE, there is unnecessary overhead.

However, since it seems that many people still use both TRUNCATE and DROP instead of only DROP, what are the advantages are to using a TRUNCATE before a DROP??

Community
  • 1
  • 1
NikkiTheCrazyAsian
  • 36
  • 6
  • Using TRUNCATE will remove all rows except schema ... using DROP both are Gone. Then why doing an extra steps. Just DROP the table directly and that's why DROP is introduced. Morover, using a TRUNCATE before a DROP is not at all any standard ... probably a low standard instead. – Rahul Jun 05 '14 at 19:10
  • @Rahul . . . I think historically `DROP` existed first and `TRUNCATE` was introduced as an efficiency mechanism for deleting all the rows in a table without logging them. Otherwise, I agree with your comment. – Gordon Linoff Jun 05 '14 at 19:14
  • Correct me if I'm wrong. You can rollback after a `DROP` but there is no rollback possible after a `TRUNCATE`. – Luc M Jun 05 '14 at 19:15
  • @LucM, that's by theory though but in some where I have red (including the link in OP's post) that both TRUNCATE/DROP can be rolled back if using inside TRANS block. – Rahul Jun 05 '14 at 19:18
  • 1
    If you understand how truncate and drop work internally I don't see how truncating before dropping could ever improve things. What's your thinking here? Drop just deallocates everything. How could the be made any more efficient? – usr Jun 05 '14 at 19:21
  • @usr: Was your comment for me or for another commenter? I understand the difference between the two, I'm just simply posing the question of are there advantages to using a TRUNCATE before a DROP? – NikkiTheCrazyAsian Jun 05 '14 at 19:29
  • @NikkiTheCrazyAsian yes, that was addressed to you. I recommend that you at least roughly research how these two command work. That allows you to easily infer the answer. I find it not helpful to memorize little things like "Should I truncate before drop?". I rather understand the internals and then answer *everything* myself. I recommend this so that you have an easier time working with SQL Server. – usr Jun 05 '14 at 19:32
  • As far as I can tell this question is entirely duplicate to the linked question. Except that you are saying "I read that, but is it *really* true?". Still a duplicate. – usr Jun 05 '14 at 19:34

1 Answers1

0

So that depends on your database, engine and version.

example: modern version of mysql execute TRUNCATE by performing a DROP and recreating the table. Older versions depending on which bug you might hit would delete the entries from the table which is much slower than dropping the table and recreating. That doesn't explain why you would TRUNCATE before DROP, but rather why you would DROP and recreate as an optimized version of truncate.

Other databases, engines, and versions may have their own peculiarities.

If there is no reason to do this with your current system, it may be this is a behavior carried over by developers who were bit by this on a different system.

cs_alumnus
  • 1,579
  • 16
  • 24