Home Ask Login Register

Developers Planet

Your answer is one click away!

Chicago1988 February 2016

Change table variable to temp table

I have a certain stored procedure written years ago. It uses a table variable. Inside the code it's doing:

declare @OpportunityVS3 as SF_OpportunityMerge
insert @TableVariable select * from /*...*/

This is inside a job that runs every five minutes. There used to be no issues in the past because the select used to collect around 10 to 1000 records… But now sometimes the select is trying to insert a million records.

Given this issue, I think I am forced to change the @TableVariable to a #TempTable. What do you think? Do I have any other option?

At the end of the SP, it’s passing the Variable to another SP:

exec [dbo].[SF_MergeOpportunity] @OpportunityVS3, @LastUpdateDate

I guess I will have to write it all in the same stored procedure, because temporary tables cannot be passed, right?

Below is the definition of the @TableVariable that is being used and the code I would have to change:

declare @OpportunityVS3 as SF_OpportunityMerge

insert  @OpportunityVS3 select Opportunity_id, Salesforce_id, AccountId, Age__c,  
-- (continues)

merge [BU2].[dbo].[Salesforce_Opportunity] as TARGET 
using ( select  Opportunity_id, 
    -- (continues)
    from @OpportunityVS3 ) as SOURCE on (TARGET.Opportunity_id = SOURCE.Opportunity_id) 

exec [dbo].[SF_MergeOpportunity] @OpportunityVS3, @LastUpdateDate

[![enter image description here][1]][1]

[![enter image description here][2]][2]


TT. February 2016

What you can try without changing the way you work:

  • Force the compiler to take cardinalities of TABLE variables into account by specifying OPTION(RECOMPILE) in queries using the TABLE variables
  • Supply a suitable UNIQUE INDEX or PRIMARY KEY for the TABLE variable

A downside of TABLE variables is that there are no statistics associated with them. The SQL compiler will produce better execution plans when it can take statistics into account. Therefore you can improve performance considerably when you switch to temporary tables because these do have statistics.

When you switch to temporary tables, you would no longer pass a TABLE variable to your stored procedure anymore. Your stored procedure would then be written using a temporary table that it knows exists beforehand - ie was created before the stored procedure is executed.

You would write your stored procedure as though the temporary table were an ordinary table. When you write the stored procedure using a temporary table, SQL Server management studio will underline certain parts in red so it appears as though there are errors in your stored procedure. But if the syntax is correct, creating/altering the stored procedure will work just fine.

Read this excellent essay on the differences between TABLE variables and temporary tables, to see how usage may impact performance. Especially topics No column statistics and Indexes.

Post Status

Asked in February 2016
Viewed 1,225 times
Voted 12
Answered 1 times


Leave an answer

Quote of the day: live life