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 |
|
rajdaksha
Aged Yak Warrior
595 Posts |
 Posted - 2009-09-15 : 07:50:49
|
| HiI have table with 5 million records. I want to delete 4.5 million records on that table so what are the stuff’s I have to consider...-------------------------R... |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-15 : 07:56:04
|
The WHERE clause 
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-15 : 08:01:56
|
But seriously!consider:The disc space needed for the log file.Are there dependencies.We don't know your system and if that is a large table for you.Maybe you can select the 500,000 rows into another table, drop and recreate the original table and reinsert the 500,000 rows...maybe it is faster, I don't really know.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-09-15 : 08:06:46
|
| I think it is better to copy the .5 million records (not qualifying for delete) to a new table and drop and re create the existing table and copy back the data. This will be more faster than the delete operation. Hope it is a one time job... |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-09-15 : 08:14:46
|
| HiScenario 1WHERE clause we don't have index on empname columnExample WHERE empname = 'raj'WHERE empname like '%raj%'WHERE empname like 'raj%'WHERE empname like '%raj'Scenario 2IF We have index on empname column. we using same below conditionWHERE empname = 'raj'WHERE empname like '%raj%'WHERE empname like 'raj%'WHERE empname like '%raj'-------------------------R... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-09-15 : 08:15:51
|
| You don't have to drop the table, just use TRUNCATE TABLE instead. It won't work if that table is referenced by a foreign key, but then neither would dropping it.The following options will get the job done, from fastest to slowest:- bcp out data you want to keep, truncate table, bcp data in- insert data you want to keep to another table, truncate table, insert data back into original table (as mentioned earlier)- use SET ROWCOUNT or DELETE (TOP) to delete rows in batches, say 50,000 - 100,000 at a time. Best if database is using Simple recovery |
|
|
|
|
|
|
|
|
DELETE