DELETE FROM vs. DROP TABLE

Dev Shed Forums Sponsor:

January 30th, 2008, 03:34 PM

sean_micken

Contributing User

Join Date: Jun 2006

Posts: 33

Time spent in forums: 12 h 5 m
Reputation Power: 8

DELETE FROM vs. DROP TABLE

I want to get rid of a huge table. DELETE FROM would take for-freaking-ever, so I was thinking of doing DROP TABLE. Would this be any faster?

If not, would doing a TRUNCATE TABLE and then DROP TABLE be fast? What's the best way to nuke a big table?

Thanks.

January 30th, 2008, 05:41 PM

marnixR

i don't do negative rep

Join Date: Jun 2006

Posts: 757

Time spent in forums: 2 Weeks 2 Days 14 h 19 m 30 sec
Reputation Power: 703

you DO realise that DROP TABLE deletes the complete table, whereas DELETE only deletes the content ?

__________________
admin of Philosophorum - now in its sixth year and still as modest as ever (although now also looking uncharacteristically professional + snazzy after the latest upgrade)

some character references
universally acclaimed as "fundie of the worst kind"
"the stubborn old loser whose high point of the day is making immature remarks and counting his rep points on an internet message board"
stop bitching about lower case - it's my trademark™

January 30th, 2008, 05:59 PM

sean_micken

Contributing User

Join Date: Jun 2006

Posts: 33

Time spent in forums: 12 h 5 m
Reputation Power: 8

Yes, I do realize that. I want to clear out a huge table and I'm trying to find a quick way to do it.

DELETE FROM would take too long and so if DROPing the table and then re-creating it is faster, that's fine with me. I just want to get rid of the data.

TRUNCATE TABLE is a quick way to delete rows from a table, but it doesn't actually remove the data. I need to way to REMOVE all that data the quickest way possible.

January 31st, 2008, 12:56 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 24,743

Time spent in forums: 3 Months 1 Day 10 h 40 m 26 sec
Reputation Power: 3826

Quote:

Originally Posted by sean_micken

TRUNCATE TABLE is a quick way to delete rows from a table, but it doesn't actually remove the data.

oh? the rows are deleted, but they're not actually removed?

perhaps you ought to follow up the TRUNCATE TABLE statement with the REMOVE ROWS statement if you're still concnerned about them

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

January 31st, 2008, 01:17 AM

sean_micken

Contributing User

Join Date: Jun 2006

Posts: 33

Time spent in forums: 12 h 5 m
Reputation Power: 8

Hahaha... funny ;p

Last edited by sean_micken : January 31st, 2008 at 02:02 AM.