delete then dropTruncate then drop/just drop

Question

Post reply

delete then dropTruncate then drop/just drop

Jules Bonnot

SSCertifiable

Points: 5488
More actions
May 4, 2009 at 1:52 pm

#209087

I have a table with millions of rows. It was used to store audit data but is no longer used. It lives in a massive DB which is under a lot of strain. What would the least resoure expensive way to delete it?
www.sql-library.com[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

well certainly not delete then drop.

It looks to me that truncate from a large table and drop a large table are in effect handled in the same way, see

http://msdn.microsoft.com/en-us/library/ms177495(SQL.90).aspx

so going directly to drop the table seems best option.

be aware all objects referencing the table would have to be deleted first, including foreign keys.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

I agree with George. Just to be sure, though, I ran a test where I build my normal million row test table and then used profiler to measure what's going on for Truncate/Drop and just DROP. Here're the results...

That's on a database with the SIMPLE recovery mode... checking on what happens with full recovery in a minute...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Here's the same tests but in the FULL recovery mode. Just DROP wins again... notice the couple of writes to the log this time...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 4

nice one jeff. So dropping a big table hardly uses any CPU at all. super

www.sql-library.com[/url]