How to efficiently delete rows while NOT using Tru

Question

Let's say we have table Sales with 30 columns and 500,000 rows. I would like to delete 400,000 in the table (those where "toDelete='1'").

But I have a few constraints :

• the table is read / written "often" and I would not like a long "delete" to take a long time and lock the table for too long
• I need to skip the transaction log (like with a TRUNCATE) but while doing a "DELETE ... WHERE..." (I need to put a condition), but haven't found any way to do this...

Any advice would be welcome to transform a

DELETE FROM Sales WHERE toDelete='1'

to something more partitioned & possibly transaction log free.

Answer 1

I have used the below to delete around 50 million records -

BEGIN TRANSACTION     
     DeleteOperation:
     DELETE TOP (BatchSize)
     FROM  [database_name].[database_schema].[database_table] 

     IF @@ROWCOUNT > 0
     GOTO DeleteOperation
COMMIT TRANSACTION

Please note that keeping the BatchSize < 5000 is less expensive on resources.