Rob-350472 (2/18/2013)
That was my decision in the end to be honest, out of business hours. Worked out okay for this situation. I guess I was just thinking if one was foced to do it in business hours if this would be better or not.I suppose one could run a simple select on the universe being updated to verify they had been updated, still, the batch/set approach would be better I guess.
For large tables, I usually do a hybrid of both...basically a while loop with an update of small batches ( It requires more code and 1 or 2 staging tables to help keep track of the records and the completed batches in case you have to stop the process). But you want to do everything in your power to avoid doing 1 row per transaction. Not only will it take forever, but it will cause rampage in your transaction log. Even if you only did 5 or 20 rows per transaction, the amount of time and transaction log IO you use is cut down is pretty significant compared to only doing 1 row at time.
But for a table that is being used during business hours, the primary goal is to make sure each transaction still happens pretty instantaneous, so of course there is a limit to how many # of rows you can include in each transaction.