title: When execution plan is changed for DELETE TOP(X) in Microsoft SQL Server?
body: <p>I observe a little strange behavior in Microsoft SQL Server 2017.
  When I <code>DELETE</code> data row by row:</p>
  
  <pre><code>DELETE TOP(1) 
  FROM [table_A] 
  WHERE [id] IN (SELECT [i] FROM [table_B])
  </code></pre>
  
  <p>it takes about 4 min. (11GB database)</p>
  
  <p><strong>but</strong> when I execute:</p>
  
  <pre><code>DELETE TOP(1000) 
  FROM [table_A] 
  WHERE [id] IN (SELECT [i] FROM [table_B])
  </code></pre>
  
  <p>on the same table, execution is very fast (&lt;&lt; 1 second).</p>
  
  <p>Difference is in execution plan.</p>
  
  <p><strong>Question:</strong> how to force SQL Server to use the execution plan from the second statement (<code>DELETE TOP(**1000**) ...</code>) for the first statement (<code>DELETE TOP(**1**) ...</code>)?</p>
  
  <p>see:</p>
  
  <p><a href="https://i.stack.imgur.com/6Vp2m.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6Vp2m.png" alt="enter image description here"></a></p>