sql INNER JOIN table variable ON VS. INNER JOIN (select) ON

Question

I'm wondering if using a table variable is more or less performant than using an inner join (select)
Example:

DECLARE @tab TABLE(Id int)  
INSERT INTO @tab  
SELECT Id  
FROM SomeTable  
WHERE SomeDate = "10 DAYS AGO" 

SELECT *
FROM SomeOtherTable
INNER JOIN @tab t
ON SomeOtherTable.id = t.id  

--VERSUS--

SELECT *  
FROM SomeOtherTable  
INNER JOIN (SELECT Id FROM SomeTable WHERE SomeDate = "10 DAYS AGO") t  
ON SomeOtherTable.id = t.id

For large queries the first is more maintainable if you have to make the same join a few times, but what is the most performant?

Greetings

Answer 1

SQL Server does not maintain detailed statistics for table variables or automatically recompile to reflect less granular cardinality information changes (without TF 2453) so will generally assume that they output a single row.

This means that sometimes you will get a sub optimal join strategy. The second version can use statistics and also avoids the overhead of inserting the intermediate results into a temporary object.

However if the second query is expensive to evaluate as SomeDate is not indexed you may get improved performance from materialising this up front (compared to repeatedly re-evaluating).

You could also consider using a #temp table as this avoids the statistics issue. Some people suggest never using a table variable in JOINs