I want to update a large table in 5000-row batches, but I don't know how to split up the data. The table doesn't include an incremental numeric or integer primary key. How can I perform the update while maintaining good performance?
If you know which rows haven't been updated and you can exclude updated rows by using a simple predicate, the ROWCOUNT setting can help you divide your update into batches. Listing 2 shows how to use this setting. ROWCOUNT causes SQL Server to stop processing the query after the specified number of rows is returned. This technique is useful because it avoids the concurrency hits that large updates incur; the smaller the x (the number of rows in the updates), the less likely that the update task will prevent other users from accessing the data. Combined with transaction-log backups, this method can also keep your transaction-log size to a minimum.
If you don't have a mechanism for identifying the rows that have been updated, you can use a cursor to iterate through the data and commit every x values. However, cursors usually hold server resources longer than set-based statements do.