SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 TRUNCATE vs DROP and RECREATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marat
Yak Posting Veteran

Australia
85 Posts

Posted - 01/20/2004 :  02:20:30  Show Profile  Reply with Quote
What is better(faster)
truncate table or drop it and recreate.
Any problems with that.
Thank you

rihardh
Constraint Violating Yak Guru

Slovenia
307 Posts

Posted - 01/20/2004 :  02:42:44  Show Profile  Reply with Quote
Truncating and dropping have nothing in common.

Exerpt from BOL:
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

BOL is full of stuff like this!
Go to Top of Page

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 01/20/2004 :  05:01:59  Show Profile  Reply with Quote
rihard, the question was which was faster. They both accomplish the same: emptying the table.

marat: I would go with truncating. Truncation is a´n unlogged operation, should be very fast. If you use foreign keys, however, you cannot use truncate and it's not good to use drop/create either.
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 01/20/2004 :  06:20:00  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Hmm. Truncate will not remove indexes / keys on the table, so if your intention is to then INSERT lots of lines, you *may* find it faster to drop the table, do a SELECT...INTO and then recreate your indexes.

But it will depend on your data sets and such.


Why not try both and see?

-------
Moo. :)
Go to Top of Page

rihardh
Constraint Violating Yak Guru

Slovenia
307 Posts

Posted - 01/20/2004 :  07:32:20  Show Profile  Reply with Quote
Door swings both ways Andraax. They don't accomplish the same thing and the statements are not comparable (DML-DDL). You can't even compare the end result because in one case you end up with an empty table and in the other with nothing!?
I wouldn't suggest dropping a table to anyone especially in this context (delete vs. drop), simply because of the fact that, if the user doesn't have the DDL to rebuild the table, he'll end up with a "fast" method to end up with nothing.
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 01/20/2004 :  08:20:27  Show Profile  Visit mr_mist's Homepage  Reply with Quote
quote:
You can't even compare the end result because in one case you end up with an empty table and in the other with nothing!?


Not quite, because he said

quote:
drop it and recreate


But still, the real test could only be done in the original poster's environment, with his or her data sets etc..

-------
Moo. :)
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/20/2004 :  08:29:04  Show Profile  Visit nr's Homepage  Reply with Quote
A drop and recreate will potentially give the table a new id and can cause problems due to dependencies. Tables are meant to be permanent and should not be changed lightly - it should be a planned thing followed by a backup and I would advise a bounce of the server or at leasat aging all plans (causes less problems with v2000 than previous versions - v6 would almost guarantee corruption but I would still avoid it).

A truncate is less likely to cause problems but you still need permissions for it (table owner?) and it will not be available for tables with foreign keys so if implemented will be restricting your design.

I would try to avoid either but definitely would not plan to drop/recreate ever.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/20/2004 :  10:53:32  Show Profile  Reply with Quote
NO SH_T

Is this because the way M$ builds/maintains their catalog?

Gooooooooooooo IDENTITY!

Wait hold the phone...there's no identity column...is sysobjects a View?

How do they manage the catalog? SELECT MAX(Id)?



Brett

8-)
Go to Top of Page

marat
Yak Posting Veteran

Australia
85 Posts

Posted - 01/20/2004 :  17:10:27  Show Profile  Reply with Quote
REF:mr_mist... Hmm. Truncate will not remove indexes / keys on the table, so if your intention is to then INSERT lots of lines, you *may* find it faster to drop the table, do a SELECT...INTO and then recreate your indexes.

But it will depend on your data sets and such.

Why not try both and see?

Ok. To make things clear, the table I am talking about is a buffer to for initial data load and processing lots af rows (100K). Because this happens often
I think in that case better is to drop and recreate table.
Am I right?
Thank you guys
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 01/20/2004 :  17:16:53  Show Profile  Reply with Quote
marat,

How are you loading the data?

If you are using BCP/BULK INSERT then TRUNCATE (Leave the index in place and specify locking)

If it is a series of INSERT/SELECT OR SELECT INTO then testing is really the only way to tell.

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/28/2004 :  17:26:46  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Hey, I haven't seen anyone mention the fact that if you DROP and RECREATE a table, you then have to re-apply all permissions, too. Unless this table is only accessed by sysadmins.

--------------------------------------------------------------
Find more words of wisdom at http://weblogs.sqlteam.com/markc
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 01/29/2004 :  05:26:53  Show Profile  Visit mr_mist's Homepage  Reply with Quote
quote:
Originally posted by AjarnMark
Hey, I haven't seen anyone mention the fact that if you DROP and RECREATE a table, you then have to re-apply all permissions, too. Unless this table is only accessed by sysadmins.



True, if you use table level permissions..

-------
Moo. :)
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 01/29/2004 :  08:56:35  Show Profile  Reply with Quote
TRUNCATE won't cause an application to fail because it can't find a table. In other words, it's an online operation. Drop/Recreate will cause things to fail because the table is just not there.

Dropping the table will be faster, as truncating can take a while even though it is non-logged because it has to deallocate all the extents allocated to the table. But use the cautions above, especially with regard to table level permissions and making sure you use the same filegroup, etc etc.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000