sql server - How to avoid UPDATE statement locking out the entire table when updating large number of records

Question

I am fairly new to locks and hints.

I have a table with very frequent SELECT and INSERT operations. The table has 11 million records.

I have added a new column to it and I need to copy over the data from an existing column in the same table to the new column.

I am planning to use ROWLOCK hint to avoid escalating locks to table level locks and blocking out all other operations on the table. For example:

UPDATE 
    SomeTable WITH (ROWLOCK)
SET
    NewColumn = OldColumn

Questions:

1. Would a NOLOCK instead of ROWLOCK? Note, once the records are inserted in the table, the value for OldColumn does not change, so NOLOCK would not cause dirty reads.
2. Does NOLOCK even make sense in this case, because the SQL Server would have to anyways get update locks for UPDATE.
3. Is there a better way of achieving this?

I know hints are to be avoided and SQL Server usually makes smarter choices, but I don't want to get the table locked out during this update.

Answer 1

Try to update in batches.

DECLARE @Batch INT = 1000
DECLARE @Rowcount INT = @Batch


WHILE @Rowcount > 0
    BEGIN
        ;WITH CTE AS 
        (
            SELECT TOP (@Batch) NewColumn,OldColumn 
            FROM SomeTable 
            WHERE NewColumn <> OldColumn
                  OR (NewColumn IS NULL AND OldColumn IS NOT NULL)
        )
        UPDATE cte
            SET NewColumn = OldColumn;
        SET @Rowcount = @@ROWCOUNT
    END