• The two UPDATEs produce the exact same exec plan.

    Just a few points:

    1) NOLOCK has no effect on the table to update

    2) NOLOCK can easily generate inconsistent data, get rid of it

    3) Updating such a large table will make you log file explode. Do it in batches and back up the transaction log between batches, something like this

    DECLARE @rows int

    DECLARE @batchsize int

    SET @rows = 1

    SET @batchsize = 10000 -- whatever size you find appropriate

    WHILE @rows > 0

    BEGIN

    UPDATE TOP(@batchsize) a

    SET a.field1 = b.field1

    FROM table1 a

    INNER JOIN table2 b

    ON a.id = b.id

    WHERE a.status = -1

    SET @rows = @@ROWCOUNT

    BACKUP LOG ...

    END

    Obviously you would have to mark the rows already processed by the batch in order to avoid updating the same records over and over.

    -- Gianluca Sartori