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

Question

Post reply

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

Viewing 15 posts - 16 through 30 (of 39 total)

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

WayneS SSC Guru Points: 95405 More actions · Answer 1

Grant Fritchey (12/2/2010)

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.

Yes indeed. And don't forget that about 99% of CTEs are essentially a predefined subquery, so repeated calls to them qualifies also. (Now a recursive CTE may be different, but still watch out for them!)

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 2

SQLNW

SSC-Addicted

Points: 433

December 3, 2010 at 7:06 am

#1258389

Thank you Craig. As Wayne said, very well defined. thanks.

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

SQLNW

SSC-Addicted

Points: 433

December 3, 2010 at 7:10 am

#1258392

Thank you Grant. It was a totally new perspective with oracle/SQL crossover. thanks,

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

SQLNW

SSC-Addicted

Points: 433

December 3, 2010 at 7:14 am

#1258398

Thanks so much Wayne for replying. I am reading more about CTEs now. thanks

sandippani SSC Eights! Points: 957 More actions · Answer 5

sandippani

SSC Eights!

Points: 957

December 5, 2010 at 12:29 pm

#1258830

Have a look to this link :

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

sandippani (12/5/2010)

Have a look to this link :

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx%5B/quote%5D

However note that the first 'fact' stated in that article is incorrect. Changes to table variables are logged. They don't participate in user transactions, but they're still logged.

http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

The second 'fact' is also incorrect in SQL 2005 and later. Use of temp tables does not automatically result in plan that's recompiled (and it certainly does not result in a plan that isn't cached at all). Even if it does, SQL 2005 and above has statement-level recompile and only the affected statements recompile, not the entire procedure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

GilaMonster (12/5/2010)

sandippani (12/5/2010)

Have a look to this link :

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx%5B/quote%5D

However note that the first 'fact' stated in that article is incorrect. Changes to table variables are logged. They don't participate in user transactions, but they're still logged.

http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

The second 'fact' is also incorrect in SQL 2005 and later. Use of temp tables does not automatically result in plan that's recompiled (and it certainly does not result in a plan that isn't cached at all). Even if it does, SQL 2005 and above has statement-level recompile and only the affected statements recompile, not the entire procedure.

I agree. Further and being a bit critical, I just don't trust people in the area of performance savy when they build a test table using a WHILE loop. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Just as a couple of a side-bars...

I've found that way too many people worry way too much about when they should use a Temp Table vs a Table Variable. I normally use Temp Tables for just one reason... they're easier to troubleshoot both during development and in the future because their content persists after a run or partial run in SSMS.

The other thing is that, while a lot of people worry about the differences and possible performance impact between the two, they typically neglect much lower hanging fruit. For example, the fellow that wrote that one article at the link previously cited in this thread... If he'll use a WHILE loop to create a simple test table, he'll use a loop for a heck of a lot more when he shouldn't. In most cases, converting loops to set based code (it's not that hard folks and becomes second nature with just a bit of practice) and making SARGable predicates in joins and WHERE clauses will return much more performance gain than the differences between types of temporary data storage.

With the idea of maintainability at heart and performance very close on the heels of that (it's usually a dead-heat in my mind), there are only 3 places where I'll use a Table Variable...

1. If (for some reason) I can't write an iTVF and must resort to an mlTVF, I'll use a table variable and that's only because SQL Server won't let me use a Temp Table in a function. Heh... that's if and only if I can't get away with some higher performance inline code to begin with. 😉

2. When I don't want a possible rollback to affect my ProcLog system (ie: logging in general).

3. IF and only IF I've proven that a Temp Table is significantly slower or causes recompiles in some very "high-hit" C.R.U.D. (think thousand of hits in very short periods of times). If I'm doing some batch processing on a couple bazillion rows of data, I actually welcome the occasional recompile so I don't actually have to worry about parameter sniffing and the like. It fact, sometimes THAT works out rather well with high-hit C.R.U.D., as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

WayneS SSC Guru Points: 95405 More actions · Answer 9

GilaMonster (12/5/2010)

sandippani (12/5/2010)

Have a look to this link :

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx%5B/quote%5D

However note that the first 'fact' stated in that article is incorrect. Changes to table variables are logged. They don't participate in user transactions, but they're still logged.

http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

The second 'fact' is also incorrect in SQL 2005 and later. Use of temp tables does not automatically result in plan that's recompiled (and it certainly does not result in a plan that isn't cached at all). Even if it does, SQL 2005 and above has statement-level recompile and only the affected statements recompile, not the entire procedure.

Two other 'facts' that are wrong

1. In SQL 2005 and later: table variables CAN be used in an insert/exec statement.

2. Table variables CAN be used in inner stored procedures or exec statements - IF they are created/populated in those procedures / statements. If the table variable is created in a parent procedure, it won't be visible in those procedures/statements (Note that I think that this is what the author meant, but it was just worded as they aren't visible in them without specifying where it was created which does make a difference.)

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 10

SQLNW

SSC-Addicted

Points: 433

December 13, 2010 at 7:59 am

#1261717

thanks for your reply sandippani.

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

SQLNW

SSC-Addicted

Points: 433

December 13, 2010 at 8:01 am

#1261718

hi everyone ,request your help urgently.

Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure

thanks for your help.

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Junglee_George (12/2/2010)

Hi

Please have a look into this article also

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

Thanks

Rather a dangerous article with false statements like

# Table variables can not have Non-Clustered Indexes

and

# You can not create constraints in table variables

which some people might be fooled into believing.

Tom

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

SQLNW (12/13/2010)

hi everyone ,request your help urgently.

Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure

thanks for your help.

That's a new problem. Please start a new post and include some better information to go on. For example, we can't do a thing to help unless you post the code that created the problem. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Tom.Thomson (12/13/2010)

Junglee_George (12/2/2010)

Hi

Please have a look into this article also

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

Thanks

Rather a dangerous article with false statements like

# Table variables can not have Non-Clustered Indexes

and

# You can not create constraints in table variables

which some people might be fooled into believing.

I'm not sure I'd call them dangerous (by themselves, they won't cause any damage, I think) but they sure are false. Heh... it's proof positive that the internet is STILL a well paved on-ramp to a dirt road. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 15

Jeff Moden (12/14/2010)

SQLNW (12/13/2010)

hi everyone ,request your help urgently.

Conversion failed when converting the varchar value '12.80' to data type int. I checked all the datatypes in my procedure

thanks for your help.

That's a new problem. Please start a new post and include some better information to go on. For example, we can't do a thing to help unless you post the code that created the problem. 😉

http://qa.sqlservercentral.com/Forums/Topic1033821-8-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass