Skip to content

Instantly share code, notes, and snippets.

@mkowoods

mkowoods/bulk-delete.sql

Last active July 29, 2019 12:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mkowoods/794d04f7047f45c05c754cc4e0e8dcaa to your computer and use it in GitHub Desktop.
Save mkowoods/794d04f7047f45c05c754cc4e0e8dcaa to your computer and use it in GitHub Desktop.
Download ZIP
Bulk Delete of SQL Rows
Raw
bulk-delete.sql
-- https://stackoverflow.com/a/28324562
-- https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
BEGIN
BEGIN TRANSACTION
-- Delete some small number of rows at a time
DELETE TOP (10000) LargeTable
WHERE readTime < dateadd(MONTH,-7,GETDATE())
SET @Deleted_Rows = @@ROWCOUNT;
COMMIT TRANSACTION
CHECKPOINT -- for simple recovery model
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment