468,243 Members | 2,036 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,243 developers. It's quick & easy.

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
Jul 20 '05 #1
2 15779
ej****@perriergroup.com (Ed) wrote in message news:<d7**************************@posting.google. com>...
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


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
Jul 20 '05 #2
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!
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ritu | last post: by
5 posts views Thread by ronin 47th | last post: by
9 posts views Thread by Sumanth | last post: by
6 posts views Thread by pramod | last post: by
8 posts views Thread by Stefan | last post: by
14 posts views Thread by Sala | last post: by
10 posts views Thread by Troels Arvin | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.