title: How TOP affects SQL performance
body: <p>I'm at a bit of a loss here. I have two nearly identical queries:</p>
  
  <pre><code>select
      top 301
      *
  from
      view
  where
      columnA like '%val%'
      and
      columnB &gt; '12/12/2019 00:00:00'
  order by
      columnC
  </code></pre>
  
  <p>and</p>
  
  <pre><code>select
      *
  from
      view
  where
      columnA like '%val%'
      and
      columnB &gt; '12/12/2019 00:00:00'
  order by
      columnC
  </code></pre>
  
  <p>When I run these two queries they work fine with the date provided.
  If I look back a little in history, the first query will effectively timeout my application, but the second query works fine.</p>
  
  <p>I did some trouble shooting, and it appears the following must be true to time out the app.</p>
  
  <ol>
  <li>top # must be included</li>
  <li>order by must be on <code>columnC</code></li>
  <li><code>columnB</code> must be sufficiently far in the past.</li>
  </ol>
  
  <p>Due to how my application is set up, the set up for the query will always include these three elements, and I am not able to move any of these elements into the view itself, so they will always be full table scans.</p>
  
  <p>I am in the process of trying to optimize my view, but I simply do not understand how adding top # to the query is causing the application to time out the way that it is.  Any help understanding how top affects SQL performance would be appreciated.</p>
  
  <p>Additional Notes:</p>
  
  <p>I have noticed that adding TOP does change the execution plan a little.  The largest difference I see in the two is that without top, the execution plan runs parallelism pretty much across every join, and this is not the case for the query containing top.</p>