2

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?

Anthony Pegram
  • 123,721
  • 27
  • 225
  • 246

2 Answers2

7

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • +0 (im out of votes or it would be +1) - better stated than mine – JNK Aug 10 '10 at 21:00
  • It seemed like a waste while I was looking at it. Maybe someone thought he/she was getting paid by the keystroke. – Anthony Pegram Aug 10 '10 at 22:42
  • @Anthony Pegram - could be... KLOC used to be a measure :) – Oded Aug 11 '10 at 07:26
  • +1, but also thinking that SP might have relevance if you want to leverage a few things that precompiling will get you, over plain old dynamic sql. But agreed, the example in the Q was kind of overkill. – code4life Apr 26 '13 at 20:48
2

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

SQLMenace
  • 132,095
  • 25
  • 206
  • 225