title: How to avoid UPDATE statement locking out the entire table when updating large number of records
body: <p>I am fairly new to locks and hints.</p>
  
  <p>I have a table with very frequent <code>SELECT</code> and <code>INSERT</code> operations. The table has 11 million records.</p>
  
  <p>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.</p>
  
  <p>I am planning to use <code>ROWLOCK</code> hint to avoid escalating locks to table level locks and blocking out all other operations on the table. For example:</p>
  
  <pre><code>UPDATE 
      SomeTable WITH (ROWLOCK)
  SET
      NewColumn = OldColumn
  </code></pre>
  
  <p>Questions:</p>
  
  <ol>
  <li>Would a <code>NOLOCK</code> instead of <code>ROWLOCK</code>? Note, once the records are inserted in the table, the value for OldColumn does not change, so <code>NOLOCK</code> would not cause dirty reads.</li>
  <li>Does <code>NOLOCK</code> even make sense in this case, because the SQL Server would have to anyways get update locks for <code>UPDATE</code>.</li>
  <li>Is there a better way of achieving this?</li>
  </ol>
  
  <p>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.</p>