A colleague of mine dropped me a IM asking how they could delete data from a table in batches of 10,000 records. The following is an example of a solution that can be used to perform a delete operation in specified batches. The solution could also be easily modified to allow an update operation to be performed in a batch.
WHILE 1 = 1
BEGIN
BEGIN TRAN
SET ROWCOUNT 10000
DELETE FROM table WHERE ind = 1
IF @@ROWCOUNT < 1
BREAK
COMMIT TRAN
END
IF @@TRANCOUNT > 0
COMMIT TRAN
