DELETING ROWS FROM VERY LARGE TABLES

Ever had a hard time deleting rows from a very large table? Try doing it in smaller chuncks in a loop, like this:

SELECT * 
INTO   #tmp 
FROM   sys.sysobjects;

DECLARE @COUNTX BIGINT;

SELECT @COUNTX =Count(*) 
FROM   #tmp;

WHILE EXISTS 
( 
       SELECT 0 
       FROM   #tmp) 
BEGIN 
  DELETE TOP (10) 
  FROM   #tmp; 
   
  SET @COUNTX=@COUNTX-10; 

  PRINT '10 DOWN!'+try_cast(@COUNTX as varchar)+' TO GO!' 
END;

DROP TABLE #tmp;

Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06