title: Should subselects in FROM clauses be optimized into derived tables in SQL Server?
body: <p>Query 1:</p>
  
  <pre><code>SELECT au_lname, au_fname, title 
  FROM (SELECT au_lname, au_fname, id FROM pubs.dbo.authors WHERE state = 'CA') as a 
  JOIN pubs.dbo.titleauthor ta on a.au_id=ta.au_id 
  JOIN pubs.dbo.titles t on ta.title_id = t.title_id
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>DECLARE @DATASET TABLE ( au_lname VARCHAR(10), au_fname VARCHAR(10), id INT );
  
  INSERT @DATASET SELECT au_lname, au_fname, id FROM pubs.dbo.authors WHERE state = 'CA';
  
  SELECT au_lname, au_fname, title 
  FROM @DATASET DATASET as a 
  JOIN pubs.dbo.titleauthor ta on a.au_id=ta.au_id 
  JOIN pubs.dbo.titles t on ta.title_id = t.title_id
  </code></pre>
  
  <p>My assumption is that these two queries are not very different from each other, from the standpoint of performance. Is Query 2 ever an improvement over Query 1?</p>
  
  <p>As a side note, I know that the subquery in this example doesn't even need to be there. This is a simplified version -- the actual query I'm dealing with is much more complicated. </p>