SET NOCOUNT ON
SET DATEFORMAT YMD
DECLARE @ROWS INT, @TOTALROWS INT, @BATCHSIZE INT
SELECT @ROWS = 1,
@TOTALROWS = 0,
@BATCHSIZE = 50
--now delete all data from main table that we have added in batches to prevent locks
WHILE @Rows > 0
--delete data in table in batches to prevent blocks
UPDATE TOP(@BATCHSIZE) MyTable
SET MyColumn = dbo.udf_SOME_FUNCTION(MyPK)
WHERE SomeDate > '2011-JAN-01'
SELECT @ROWS = @@ROWCOUNT, @TOTALROWS = @TOTALROWS + @ROWS
PRINT 'Updated ' + CAST(@ROWS as varchar) + ' in batch'
PRINT 'Updated ' + CAST(@TOTALROWS as varchar) + ' total rows'
I am currently using this process now to update a table that is constantly being accessed by a busy website that has over a million rows in it and it isn't causing any BLOCKING or performance issues at all.