Converting Temp Table to Table Variable... Is this possible and how?
-
My company is moving from SQL 2000 to SQL 2008.
Along with that jump they have implemented a new security requirement that prevents us from using all the temp tables that have already been created in 2000. They are now directing us to use Table Variables.
Does anyone know if it is even possible to convert the temp tables to table variables and how this would be done?
Any help at all would be greatly appreciated.
Thanks!!
~Audrey
-
You go into every proc that has temp tables in it, and you rewrite it to use table variables. Change "create table #name" to "declare @name table", and search-and-replace "#" with "@".
I'm going to hazard a guess that the person who created that rule has no foggiest clue what they're talking about.
The most likely end result is that you will significantly degrade performance on those procs, after spending a lot of time rewriting them, and will have zero increase in security or anything else.
The rule doesn't actually help much of anything. Table variables are created in tempdb just like temp tables, they just don't have the same naming convention, so it doesn't even reduce access to tempdb to do this. All it does is reduce performance by getting rid of stats on the tables, eliminating the possibility of various index types on them, and so on. It does remove activity in them from the transaction log, which can be good in a few circumstances, but is more often either completely unimportant or actually bad.
Edit: Almost forgot, temp tables can be accessed by sub-procs if they are created in the calling proc. Table variables can't. You'll have to work around that by passing them back and forth as parameters if you have any code that does that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Wonderful!!!
Thank you for the wicked quick response!!!
-
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
There's an article coming out soon (ask Steve, not me, when that will be) that will tell you everything that you need to know in comparing table variables to temporary tables. When it comes out, you ought to print it out and give it to your boss.
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 -
WayneS (4/30/2009)
There's an article coming out soon (ask Steve, not me, when that will be) that will tell you everything that you need to know in comparing table variables to temporary tables. When it comes out, you ought to print it out and give it to your boss.Until it's published you can make do with this brief summary - http://sqlinthewild.co.za/index.php/2007/12/19/temp-tables-and-table-variables/
Bottom line: Table vars tend to have unpleasant performance characteristics if they have a lot of rows. The also can't have indexes (other than pk and unique constraints)
btw, table variables exist in SQL 2000 too,
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Having reviewed the article, I highly agree with the comment to print the article and give it to your boss.
-
Audrey,
Just as an FYI to illustrate the performance difference between table variables and temp tables...
Tonight I was working on a new piece of code.... just getting the concept down before tweaking it.
I was using a table variable to hold 36 incrementing numbers, and 36 dates. (2 columns, 36 rows) with a PK on the date field, and a unique constraint on the number. This gives me indexes on both columns. What more could you ask for?
I was joining this to some 40,000 records.
With a table variable, it was taking 3 min, 30 secs.
I changed the table variable to a temp table, and the references from the table variable to the temp table. THIS WAS THE ONLY CHANGE! It now runs in just 3 seconds.
Just to show that the faster temp table wasn't due to caching, I switched it back to the table variable, and the time went back to 3:30.
So, with using the temp table, and in this circumstance, this is something like 70 times improvement in performance.
You should always check both ways with your code. Sometimes (rarely), you might get better performance out of a table variable. But, usually, you won't.
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 -
Further to this I just got the following reply from Paul White on another thread:
Paul White (4/30/2009)
SQL2K5 caches 1 data page and one index allocation map page for a temporary table with the query plan.The table is only actually created once, even if you run the procedure in a loop.
See this brilliant article by Sunil Agarwal for a fuller explanation and a repro script:
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx
Paul
This blog post explains that temp tables don't necessarily cause a recompilation either, which is one reason people want to use table variables instead.
Original Thread
http://www.sqlservercentral.com/Forums/Topic707743-338-1.aspx
Tim
.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply