How to delete 1 million rows from tables in SQL Server ?

Question

Please provide the efficient solution with query ?

Vishal Yelve · Answer

Please check this link it might help https://www.mssqltips.com/sqlservertip/6238/fastest-way-to-delete-large-number-of-records-in-sql-server/ @sachin I also think deleting in one go will be faster.

Jaydeep Patil · Answer

Check this link https://dba.stackexchange.com/questions/189607/delete-millions-of-rows-from-a-sql-table

Vishal Joshi · Answer

@sachine it's for the sapce. if we delete in single sort then it will acquire log file size more than chunk. so if we are expecting to free size of disk to free with delete record then we should go with chunks other wise single sort is better. please find below link for refrence and example. https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes#top

Sachin Singh · Answer

I have never tested it but i think deleting record in one go will always be faster than deleting in chunks. @Vishal are you sure it would be faster than deleting all at once?

Vishal Joshi · Answer

DECLARE @BATCHSIZE INT @WAITFORVAL VARCHAR(8) @ITERATION INT @TOTALROWS INT @MAXRUNTIME VARCHAR(8) @BSTOPATMAXTIME BIT @MSG VARCHAR(500) SET DEADLOCK_PRIORITY LOW; SET @BATCHSIZE = 4000 SET @WAITFORVAL = '00:00:10' SET @MAXRUNTIME = '08:00:00' -- 8AM SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME SET @ITERATION = 0 -- LEAVE THIS SET @TOTALROWS = 0 -- LEAVE THIS WHILE @BATCHSIZE>0 BEGIN -- IF @BSTOPATMAXTIME = 1 THEN WE'LL STOP THE WHOLE JOB AT A SET TIME... IF CONVERT(VARCHAR(8)GETDATE()108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1 BEGIN RETURN END DELETE TOP(@BATCHSIZE) FROM SOMETABLE WHERE 1=2 SET @BATCHSIZE=@@ROWCOUNT SET @ITERATION=@ITERATION+1 SET @TOTALROWS=@TOTALROWS+@BATCHSIZE SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) RAISERROR (@MSG 0 1) WITH NOWAIT WAITFOR DELAY @WAITFORVAL END DECLARE @BATCHSIZE INT @WAITFORVAL VARCHAR(8) @ITERATION INT @TOTALROWS INT @MAXRUNTIME VARCHAR(8) @BSTOPATMAXTIME BIT @MSG VARCHAR(500) SET DEADLOCK_PRIORITY LOW; SET @BATCHSIZE = 4000 SET @WAITFORVAL = '00:00:10' SET @MAXRUNTIME = '08:00:00' -- 8AM SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME SET @ITERATION = 0 -- LEAVE THIS SET @TOTALROWS = 0 -- LEAVE THISWHILE @BATCHSIZE>0 BEGIN-- IF @BSTOPATMAXTIME = 1 THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...IF CONVERT(VARCHAR(8)GETDATE()108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1BEGINRETURNENDDELETE TOP(@BATCHSIZE)FROM SOMETABLEWHERE 1=2SET @BATCHSIZE=@@ROWCOUNTSET @ITERATION=@ITERATION+1SET @TOTALROWS=@TOTALROWS+@BATCHSIZESET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)RAISERROR (@MSG 0 1) WITH NOWAITWAITFOR DELAY @WAITFORVAL END