Truncate Table vs. Drop Table

[Ed]

I am trying to get some information to compare and contrast the
Truncate Table function and the Drop Table function. I know that
using Truncate Table is faster and saves the structure of the table
while the Drop Table will delete the table totally (data and
structure). My question is whether using one function over the other
will use up more capacity on the server?

Ed

[Simon Hayes]

Re: Truncate Table vs. Drop Table

ejones@perrierg roup.com (Ed) wrote in message news:<d7da607f. 0401141059.40b8 f754@posting.go ogle.com>...[color=blue]
> I am trying to get some information to compare and contrast the
> Truncate Table function and the Drop Table function. I know that
> using Truncate Table is faster and saves the structure of the table
> while the Drop Table will delete the table totally (data and
> structure). My question is whether using one function over the other
> will use up more capacity on the server?
>
> Ed[/color]

A quick test (on a smallish table) suggests that DROP TABLE requires
more CPU and I/O. I would guess this is because not only are the
extents deallocated (which also happens with TRUNCATE TABLE), but the
metadata about the table and indexes in sysobjects, syscolumns etc.
has to be removed.

Personally, I'd say that in most cases, the work required to rebuild a
table (retrieve and execute scripts for the table plus constraints,
triggers etc) is probably a bigger consideration. But I'm more or less
guessing - the only way to get a clear answer for your situation is to
test it and see.

Simon

[Paul Scotchford]

Re: Truncate Table vs. Drop Table

IMHO ... Well DROP Table is good if it is a big table that has indexes
and you plan to load it up with data (eg. 100's thousands of rows)
frequently. Dropping the table , loading it and then applying the
indexes appears faster, and you can script that of course.

As where truncate is a quick delete of all rows etc from the table.

So depending on your requirements Id advise benchmarking both mechanisms
for the table that you want to work with.





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!