| Author |
Topic |
|
marat
Yak Posting Veteran
85 Posts |
 Posted - 2004-01-20 : 02:20:30
|
| What is better(faster)truncate table or drop it and recreate.Any problems with that.Thank you |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-01-20 : 02:42:44
|
| 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! |
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2004-01-20 : 05:01:59
|
| 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. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-01-20 : 06:20:00
|
| 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. :) |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2004-01-20 : 07:32:20
|
| 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. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-01-20 : 08:20:27
|
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 saidquote:
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. :) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-20 : 08:29:04
|
| 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. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-20 : 10:53:32
|
| NO SH_TIs 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)?Brett8-) |
|
|
|
marat
Yak Posting Veteran
85 Posts |
Posted - 2004-01-20 : 17:10:27
|
| 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 oftenI think in that case better is to drop and recreate table.Am I right?Thank you guys |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-01-20 : 17:16:53
|
| 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.." |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-01-28 : 17:26:46
|
| 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 [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-01-29 : 05:26:53
|
quote:
Originally posted by AjarnMarkHey, 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. :) |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-29 : 08:56:35
|
| 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. |
|
|
|
|
TRUNCATE vs DROP and RECREATE