How do I know when to use temp tables/table variables?

Question

How do I know when to use temp tables/table variables?

SQLNW

SSC-Addicted

Points: 433
More actions
December 1, 2010 at 2:46 pm

#104010

Could you please tell me what kind of queries need temp or table variable table? thanks.

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Table variables can't use statistics, so the optimizer treats them as having just one row. Even if you use the OPTION RECOMPILE hint, it knows nothing about the uniqueness/density of the data, so it makes a guess as to how many records it will use. They can lead to poorly performing execution plans - I've seen some that ran poorly with less than 50 records in them. Most of the gurus on this site use local temporary tables whenever possible.

Global temporary tables can be seen and modified by other sessions. IMO, you need a real good reason to use them.

Which leaves local temporary tables to use. There are places where you can't use them (functions, parameters to procedures, etc.), but if you can, use a local temporary table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 2

Wayne, you always respond with such terrific answers.

I'm glad to know most people usually still use temp tables rather than variables, I was afraid I was just being "old school" about it. 😀

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

The one place where I consider using table variables is when I'm having issues with excessive, or long running, recompiles on a query. In that instance, I might choose to pay the cost of having the statistics-free table variable within the query because I avoid the recompiles frequently caused by temporary tables. Or, if I know that I'm only ever going to have a very low number of rows in temporary storage, I might use a table variable. Finally, if I'm not going to JOIN or WHERE against the temp storage, again, I might use a table variable.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WayneS SSC Guru Points: 95460 More actions · Answer 4

mtillman-921105 (12/1/2010)
Wayne, you always respond with such terrific answers.
I'm glad to know most people usually still use temp tables rather than variables, I was afraid I was just being "old school" about it. 😀

:blush: Thanks. (But I've been known to ask some pretty stupid questions...)

I would think that anyone that has done the testing would normally use temp tables. However, the majority of people haven't done this... so who knows which is more prevalent?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 5

Hi

Please have a look into this article also

http://www.mssqltips.com/tip.asp?tip=1556

Thanks

WayneS SSC Guru Points: 95460 More actions · Answer 6

Junglee_George (12/2/2010)
Hi
Please have a look into this article also
http://www.mssqltips.com/tip.asp?tip=1556
Thanks

This is an interesting article; however there are several things that are just flat-out wrong with it, especially pertaining to table variables. Please see this article[/url] that I wrote instead.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 7

WayneS (12/2/2010)
Junglee_George (12/2/2010)
Hi
Please have a look into this article also
http://www.mssqltips.com/tip.asp?tip=1556
Thanks
This is an interesting article; however there are several things that are just flat-out wrong with it, especially pertaining to table variables. Please see this article[/url] that I wrote instead.

Note to self... If tempted to suggest that someone read an article about x, make sure said someone has not actually written an article about x themselves. :laugh:

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

WayneS SSC Guru Points: 95460 More actions · Answer 8

mtillman-921105 (12/2/2010)
Note to self... If tempted to suggest that someone read an article about x, make sure said someone has not actually written an article about x themselves. :laugh:

To be fair, I didn't take it to be aimed at me, but the OP.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLNW SSC-Addicted Points: 433 More actions · Answer 9

Thanks Wayne, Mtillman , Geaorge and everyone who responded to my post. Its such a great learning with you gurus. I am new here and I am practicing using temp dbs and if you guys have any links to scripts that use temp tables in stored procedure, please let me know. I dont know how to use a format . thanks again.

SQLNW SSC-Addicted Points: 433 More actions · Answer 10

Thanks so much Grant. I have a silly question. when do you actually know when to use temp table concept in the first place, I mean if you look at a problem, how do you decide that you'd have to use temp table (condition of join required or what?). Any example against Adventureworks DB would be great. thanks.

SQLNW SSC-Addicted Points: 433 More actions · Answer 11

Thank you Wayne, I am thinking why do we use temp table in the first place? As soon as you see a problem how you decide you need to use a temp table? Is it like when we need to make changes to a record set /values and not affect the actual data in the actual table, and return the results of the temp table..

Am I right ? and what kind of real life examples justify the usage of temp table. Please let me know. Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

SQLNW (12/2/2010)
Thank you Wayne, I am thinking why do we use temp table in the first place? As soon as you see a problem how you decide you need to use a temp table? Is it like when we need to make changes to a record set /values and not affect the actual data in the actual table, and return the results of the temp table..
Am I right ? and what kind of real life examples justify the usage of temp table. Please let me know. Thanks.

There's a few places I'll use it.

1) I have a very small resultant data set from a portion of a query, but the optimizer isn't realizing that quickly enough for my tastes, and is slowing down my query. I'll drop the data to a temp table (where it'll seek for that data properly, to get it), then bring the temp table in with an index or two on it that matches the rest of the involved tables to speed the process.

2) If I'm doing a quirky/serial update and I need to remap clustered indexing, or I'm dealing with multiple tables to get my result set.

3) Repeated use of a subquery. Sometimes you need to pull, say, a list of aggregations out of a database and then do a few things with the results. Instead of rerunning the query repeatedly, drop it to a #tmp so you only have to run it once and then constantly re-use the result set.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 13

SQLNW (12/2/2010)
Thanks so much Grant. I have a silly question. when do you actually know when to use temp table concept in the first place, I mean if you look at a problem, how do you decide that you'd have to use temp table (condition of join required or what?). Any example against Adventureworks DB would be great. thanks.

And that's where things get tough. I don't have a hard and fast rule I can give you. In general, I try to do everything in a set-based fashion. Even if that means joining 20-30 tables together. But, at some point, it becomes clear that the data is not going to come together in a single statement, I will look to break it down into multiple statements.

Also, if I'm doing cross-server queries to Oracle, I will automatically assume a temp table, filling it with the query to oracle and then joining it to my data in SQL Server.

Other than that... nothing is coming to mind.

Sorry that's not more precise. Let's just say, I use them rarely and regularly tune procedures by eliminating them. They're not exactly a standard tool I use.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 14

Craig Farrell (12/2/2010)
SQLNW (12/2/2010)
Thank you Wayne, I am thinking why do we use temp table in the first place? As soon as you see a problem how you decide you need to use a temp table? Is it like when we need to make changes to a record set /values and not affect the actual data in the actual table, and return the results of the temp table..
Am I right ? and what kind of real life examples justify the usage of temp table. Please let me know. Thanks.
There's a few places I'll use it.
1) I have a very small resultant data set from a portion of a query, but the optimizer isn't realizing that quickly enough for my tastes, and is slowing down my query. I'll drop the data to a temp table (where it'll seek for that data properly, to get it), then bring the temp table in with an index or two on it that matches the rest of the involved tables to speed the process.
2) If I'm doing a quirky/serial update and I need to remap clustered indexing, or I'm dealing with multiple tables to get my result set.
3) Repeated use of a subquery. Sometimes you need to pull, say, a list of aggregations out of a database and then do a few things with the results. Instead of rerunning the query repeatedly, drop it to a #tmp so you only have to run it once and then constantly re-use the result set.

Ooh. I like #3. Good idea & well defined.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning