'Deleting 1 millions rows in SQL Server

I am working on a client's database and there is about 1 million rows that need to be deleted due to a bug in the software. Is there an efficient way to delete them besides:

DELETE FROM table_1 where condition1 = 'value' ?
sql-server


Solution 1:[1]

BEGIN TRANSACTION     
    DoAgain:
    DELETE TOP (1000)
    FROM <YourTable>
    IF @@ROWCOUNT > 0
    GOTO DoAgain
COMMIT TRANSACTION

Solution 2:[2]

Maybe this solution from Uri Dimant

WHILE 1 = 1
BEGIN
   DELETE TOP(2000)
   FROM Foo
   WHERE <predicate>;
   IF @@ROWCOUNT < 2000 BREAK;
END

(Link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5225ca7-f16a-4b80-b64f-3576c6aa4d1f/how-to-quickly-delete-millions-of-rows?forum=transactsql)

Solution 3:[3]

Here is something I have used:

  1. If the bad data is mixed in with the good- 

    INSERT INTO #table 
   SELECT columns 
   FROM old_table 
   WHERE statement to exclude bad rows

TRUNCATE old_table

INSERT INTO old_table 
   SELECT columns FROM #table

Solution 4:[4]

Not sure how good this would be but what if you do like below (provided table_1 is a stand alone table; I mean no referenced by other table)

  1. create a duplicate table of table_1 like table_1_dup

  2. insert into table_1_dup select * from table_1 where condition1 <> 'value';

  3. drop table table_1

  4. sp_rename table_1_dup table_1

Solution 5:[5]

If you cannot afford to get the database out of production while repairing, do it in small batches. See also: How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table

If you are in a hurry and need the fastest way possible:

  • take the database out of production
  • drop all non-clustered indexes and triggers
  • delete the records (or if the majority of records is bad, copy+drop+rename the table)
  • (if applicable) fix the inconsistencies caused by the fact that you dropped triggers
  • re-create the indexes and triggers
  • bring the database back in production

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 sansalk
Solution 2 Tung Nguyen
Solution 3
Solution 4 Rahul
Solution 5 Community

Related Questions