title: TOP performance issue
body: <p>I have this query, actually it is similar to this, this is for testing purpose:</p>
  
  <pre><code>SELECT
  [Distinct1].[ProspectID] AS [ProspectID]
  FROM ( SELECT DISTINCT 
      [Limit1].[ProspectID] AS [ProspectID]
      FROM ( SELECT TOP 10
          [Extent1].[ProspectID] AS [ProspectID]
          FROM [sqlsolvent].[crm_SearchTable] AS [Extent1]
          WHERE ([Extent1].[CP_Name] LIKE '%dsd%' ESCAPE N'~') 
      )  AS [Limit1]
  )  AS [Distinct1]
  </code></pre>
  
  <p>This is the execution plan for it:</p>
  
  <p><a href="https://i.stack.imgur.com/KBiNp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KBiNp.png" alt="enter image description here"></a></p>
  
  <p>And when I remove <code>TOP 10</code> from query I get this execution plan:</p>
  
  <p><a href="https://i.stack.imgur.com/PWlwU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PWlwU.png" alt="enter image description here"></a></p>
  
  <p>The second execution plan without <code>TOP 10</code> is 2 times faster, can someone explain why TOP changes execution plan so much and why it so slower? It is the same even if query doesn't return any result, shouldn't top just be aplied to the result set, why does it have so much impact on performance when query returns nothing?</p>