CODEWITHSUNDEEP
sql-serverstored-procedurestable-variable
Anthony Pegram
Anthony Pegram

Reputation: 126992

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

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?

Upvotes: 2

Views: 871

Answers (2)

Oded
Oded

Reputation: 499382

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.

Upvotes: 7

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 2

Related Questions