Using table variables in stored procedures versus merely selecting from tables or a view?

Question

I'm looking at sprocs right now that seem to follow the behavior demonstrated below

DECLARE @tablevar TABLE
(
    FIELD1   int,
    FIELD2   int,
    FIELD3   varchar(50),
    -- etc
)

INSERT INTO @tablevar
(
    FIELD1,
    FIELD2,
    FIELD3,
    -- etc
)
SELECT FIELD1, FIELD2, FIELD3, -- etc
FROM
TableA Inner Join TableB on TableA.Foo = TableB.Foo
Inner Join TableC on TableB.Bar = TableC.Bar
-- Where, Order By, etc.

Select FIELD1, FIELD2, FIELD3, -- etc
FROM @tablevar

Is there a benefit to using this approach as opposed to using a plain select statement and skipping the table variable?

Answer 1

I don't see any benefit in doing this if all you are doing is populating the table and selecting from it

Answer 2

If you plan on using it exactly as posted (populating it then selecting the result), you are not gaining anything. You are just taxing you SQL Server, requiring more CPU and memory usage.

The answer changes if you are planning on doing more with the table variable, and depends on how you may use it. If it will hold a small amount of data, it may be more efficient to use it on subsequent statements. If you intend to use it multiple times and the initial population is expensive, then it might be more efficient.

I keep saying maybe, as each situation is different and you will need to test in order to see if it makes a difference.

Answer 3

Table Variables are created in tempdb not just held in memory as is often believed.

They don't have the logging overhead of #temp tables or overhead of statistics creation but are still not free.

All I can see are disadvantages to this practice.