Is it better (faster) to use variables, or store values in a temp table or table var?

I have some UDFs that I am trying to clean up that use a series of IF statements to return the status of something in real time.

These functions all start off by declaring 15-20 variables (varchars, ints), selecting values into them, then cycles through the IF statements.

Would there be a benefit to storing the values in a temp table or table var versus creating, populating and referencing all of these variables?


The UDFs currently in existence are actually scalar functions built by another developer, no longer with the project. The variables are populated from a single select with an average of 5 joins. I have inherited about 10 of these functions. Some reports hit two or more of them, and some times the same function is hit once in the select statement and again in the where clause.

more ▼

asked Oct 21, 2009 at 03:11 PM in Default

avatar image

Rob Allen
34 5 4 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Without seeing your code, I think multiple variables is probably better.

I think the key for performance is the part when you are "selecting values into them". Fewer queries is obviously better. If you want to set multiple variables with a single select, you can insert the values into a table variable as you suggested, or you can just include multiple assignments in the SELECT clause.

SELECT @FirstVar = first_col, @SecondVar = second_col
FROM MyReferenceTable

For code readability, I would think that multiple variables are better. Also, if the values are stored inside a table variable, then your IF statements would have to query that table each time.

more ▼

answered Oct 21, 2009 at 04:03 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left

Also, Starting with sql server 2008 you can declare and set a value to any variable, like:

declare @a int = 0, @b int = 1, @c int = 2

Have you though about splitting your functions and call them from your main, making more specialized funcions/procedures instead of those big monsters of 200+ lines is often a great idea. This way each function/procedure could require less variables.

more ▼

answered Oct 21, 2009 at 04:27 PM

avatar image

592 4 6 11

(comments are locked)
10|1200 characters needed characters left

Without seeing the code, I'd go with variables. Temporary tables or table variables carry quite a bit of overhead to allocate space for them, load them, in the case of temporary tables, create and maintain statistics, again in the case of temporary tables, deal with recompiles, and with table variables deal with the fact that if you're putting more than a few rows in them, they lead to serious performance bottlenecks.

These UDF's you're creating, they don't access data through all these various IF statements do they? If so, you're possibly looking at some serious performance issues due to recompiles. Are they multi-statement table valued UDF's? These are extremely dangerous for performance since they work off the mechanism of table variables, with no statistics, each one is treated as if it were a single row by the optimizer.

more ▼

answered Oct 21, 2009 at 08:11 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 21, 2009 at 03:11 PM

Seen: 2931 times

Last Updated: Oct 22, 2009 at 10:21 AM

Copyright 2018 Redgate Software. Privacy Policy