title: Using temp table for sorting data in SQL Server
body: <p>Recently, I came across a pattern (not sure, could be an anti-pattern) of sorting data in a SELECT query. The pattern is more of a verbose and non-declarative way for ordering data. The pattern is to dump relevant data from actual table into temporary table and then apply orderby on a field on the temporary table. I guess, the only reason why someone would do that is to improve the performance (which I doubt) and no other benefit.</p>
  
  <p>For e.g. Let's say, there is a user table. The table might contain rows in millions. We want to retrieve all the users whose first name starts with 'G' and sorted by first name. The natural and more declarative way to implement a SQL query for this scenario is:</p>
  
  <p><strong>More natural and declarative way</strong></p>
  
  <pre><code>SELECT * FROM Users
  WHERE NAME LIKE 'G%'
  ORDER BY Name
  </code></pre>
  
  <p><strong>Verbose way</strong></p>
  
  <pre><code>SELECT * INTO TempTable
  FROM Users
  WHERE NAME LIKE 'G%'
  
  SELECT * FROM TempTable
  ORDER BY Name
  </code></pre>
  
  <p>With that context, I have few questions:</p>
  
  <ol>
  <li><p>Will there be any performance difference between two ways if there is <strong>no index</strong> on the first name field. If yes, which one would be better.</p></li>
  <li><p>Will there be any performance difference between two ways if there is an <strong>index</strong> on the first name field. If yes, which one would be better.</p></li>
  <li><p>Should not the SQL Server optimizer generate same execution plan for both the ways?</p></li>
  <li><p>Is there any benefit in writing a verbose way from any other persective like locking/blocking?</p></li>
  </ol>
  
  <p>Thanks in advance.</p>