Multi Rows Deletion from table in SQL Server

Question

How I can Delete 1.5 Millions Rows From SQL Server 2000, And how much time it will take to complete this task.

I dont want to delete all records from table.... I just want to delete all records which are fullfilling WHERE condition.

EDITED from a comment to an answer below.

"I fire the same query i.e. delete from table_name with Where Clause... Is it possible to Disable Indexing at the running Query, becuase Query is going on from past 20 hr.. Also help me out how i can disable Indexing.."

Answer 1

Delete from table where condition for those 1.5 million rows

The time depends.

Answer 2

On Oracle it is also possible to use

truncate table <table>

Not sure if that is standard SQL or available in SQL Server. It will however clear the whole table - but then it is quicker than "delete from " (it will also conduct a commit).

Answer 3

If (and only if) you want to delete all of the records in a table, you can use DROP TABLE or TRUNCATE TABLE.

DELETE removes one record at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE is much faster because it doesn't record the activity in the transaction log. It removes all rows from a table, but the table structure & its columns, constraints, indexes and so on remain. DROP TABLE would remove those.

Use caution if you decide to TRUNCATE. It's irreversible (unless you have a backup).

Answer 4

TRUNCATE will also ignore any referential integrity or triggers on the table. DELETE FROM ... WHERE will respect both. The time will depend on the indexing of your condition columns, your hardware, and any additional system load.

Answer 5

create a second table, inserting all rows from the first that you don't want deleting.

delete the first table

rename the second table to be the first

(or a variation on the above)

This can often be quicker than doing a delete of selected records from a big table.

Answer 6

DELETE FROM table WHERE a=b;

When deleting that many rows you may want to disable the indexes so they don't get updated on every delete. Rewriting the indexes on every deletion will significantly slow down the whole process.

You'll want to disable these indexes before beginning your deletion or else there may be table locks already in place.

--Disable Index
ALTER INDEX [IX_MyIndex] ON MyTable.MyColumn DISABLE

--Enable Index
ALTER INDEX [IX_MyIndex] ON MyTable.MyColumn REBUILD

If you wish to remove all entries in a table you can use TRUNCATE.

Answer 7

The delete SQL is exactly the same as a normal SQL delete

delete from table where [your condition ]

However if your worried about time then I'll assume your question is a little deeper than this. If your table is has a significant number of non-clustered indexes then in some circumstances it may be faster to drop all these indexes first and rebuild after the delete. This is unusual but in cases where your straightforward delete is vulnerable to timeout issues it may be helpful

Answer 8

You may want to try deleting in batches too. I just tested this on a table I have and the delete operation went from 13 seconds to 3 seconds.

While Exists(Select * From YourTable Where YourCondition = True)
  Delete Top (100000)
  From   YourTable
  Where  YourCondition = True

I don't think you can use the TOP predicate if you are running SQL2000, but it works with SQL2005 and up. If you are using SQL2000, then you can use this syntax instead:

Set RowCount 100000
While Exists(Select * From YourTable Where YourCondition = True)
   Delete 
   From YourTable
   Where  YourCondition = True

Answer 9

Does the table you are deleting from have multiple foreign keys, or cascaded deletes or triggers? All of these will impact performance.

Depending on what you want to do and the transactional integrity, can you delete things in small batches e.g. if you are trying to delete 1.5 million records that is 1 years worth of data, can you do it 1 week at a time?

Answer 10

CREATE TABLE new_table as select <data you want to keep> from old_table;

index new_table
grant on new table
add constraints on new_table
etc on new_table

drop table old_table
rename new_table to old_table;