Should subselects in FROM clauses be optimized into derived tables in SQL Server?

Question

Query 1:

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

Query 2:

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

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?

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.

Answer 1

If you have more than a couple rows of data, query 2 is in all likelihood worse than query 1.

• You are SELECTing the data twice - once into the table variable and once again to return it
• Table variables generally do not perform great - they have no stats or indexes and contrary to popular belief still write to tempdb and the log

If it were me I would rewrite the first query without a derived table, which is really unnecessary:

SELECT 
    au_lname
    ,au_fname
    ,title 
FROM 
    pubs.dbo.authors a 
INNER JOIN 
    pubs.dbo.titleauthor ta 
        on a.au_id=ta.au_id 
INNER JOIN 
    pubs.dbo.titles t 
        on  ta.title_id = t.title_id
WHERE 
    a.state = 'CA'

Note For more info than you ever wanted on table variables and temp tables, see this epic answer from Martin Smith.

Answer 2

The only thing Query 2 buys you is the ability to store additional data in the table variable (for instance, if your procedure generates extra meta data to support its purpose). What it costs you is the ability to use any indexes on the physical table.