Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Arijit
Starting Member
25 Posts |
 Posted - 2006-03-09 : 02:28:08
|
| Hi,I am really confused on this matter.I have a table with 2 millions rows. Every weekend I need to delete the rows. Now I have the query from optimization point.I have two points.1. I can truncate the table.2. I can Drop and Recreate the Table.Now please suggest me which one is the best point 1/2 in terms of performance. Thanks in advance.RegArijit |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-03-09 : 03:13:03
|
| 1. do you need to delete all of the rows?2. are there dependencies/constraints/triggers/sprocs etc referencing to it?3. is it a transactional table or maintenance table?if there are really dependencies, i'll go for truncate2 million rows is just a few minutes or seconds even for this type of process--------------------keeping it simple... |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-09 : 03:25:01
|
| If you want to delete all the rows every weekend, then how would you get those in future?If you dont need them at all, then truncate is the best optionMadhivananFailing to plan is Planning to fail |
|
|
|
Arijit
Starting Member
25 Posts |
Posted - 2006-03-09 : 03:49:48
|
| Hi,Thanks for your answers.I dont have any dependencies with other objects with this table.RegArijit |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-09 : 05:41:11
|
| "I dont have any dependencies with other objects with this table"TRUNCATE TABLE then ... it will work regardless of what DDL changes there are to that table in the future - whereas if you DROP / RECREATE you then have a potential maintenance problem of having to keep the CREATE TABLE statement in step with any structural changes to the table in the future.Kristen |
|
|
|
|
|
|
Truncate/Recreate