Friday 29 June 2012

Query to delete large sets of records

I have found it useful when doing deletes from table with a large number of rows to delete rows in batches of say 5000 or so (I usually test to see which value works the fastest, sometimes it's 5000 rows, sometimes 10,000, etc.).
This allows each delete operation to complete quickly, rather than waiting a long time for one statement to knock out 400 million records.
In SQL Server 2005, something like this should work

WHILE EXISTS ( SELECT 1 FROM EventLog WHERE EventLogid < 8700000000)
BEGIN
  DELETE TOP(5000) FROM EventLog WHERE EventLogid < 8700000000

END

No comments:

Post a Comment