title: UPDATING a ROW with TOP and WHERE
body: <p>I am planning to implement a kind-of work queue for users using only a SQL Server table. A row will be assigned to only one user at a time. I was planning to go a common <code>UPDATE SET</code> .. <code>WHERE</code> .. route, like has been proposed in this question:</p>
  
  <p><a href="https://stackoverflow.com/a/9241466">https://stackoverflow.com/a/9241466</a></p>
  
  <p>Here is the relevant part, quoted:</p>
  
  <pre><code>;WITH CTE AS 
  ( 
      SELECT TOP 100 * 
      FROM T1      
      ORDER BY F2 
  ) 
  UPDATE CTE SET F1='foo'
  </code></pre>
  
  <p>To ensure only one user actually gets to edit a row, I will include an additional property, UserId, and check for <code>NULL</code> in a <code>WHERE</code> clause. </p>
  
  <pre><code>;WITH CTE AS 
  ( 
      SELECT TOP 5 * 
      FROM T1
      WHERE UserId IS NULL      
      ORDER BY F2 
  ) 
  UPDATE CTE SET UserId = @userid
  </code></pre>
  
  <p>After statement completion, I will <code>SELECT</code> the affected rows from the database.</p>
  
  <p>However, I might want to include some additional properties in my <code>WHERE</code> clause, and also some additional ordering. </p>
  
  <p><code>UPDATE</code> will lock the row if the <code>WHERE</code> is included, but is this still the case for when the <code>WHERE</code> is included in the <code>CTE</code>? </p>
  
  <p>My question is, differently phrased, will <code>UPDATE</code> still lock the rows or is there a chance that two concurrently run statements will overwrite the UserId because of a racing issue?</p>