MSSQL Mass Delete

To avoid holding up everything else when deleting massive amounts of data from an SQL Server table, you can use the TOP() function to delete the data in chunks.

SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP(1000) FROM [dbo].[mytable]
WHERE timestampUtc < '2015-05-29'
END

The “SELECT 1” primes the @@ROWCOUNT variable, so the while loop will run. Yes, there are indeed prettier ways of doing this, but it doesn’t really matter.

Leave a Reply

Your email address will not be published. Required fields are marked *