Which is Best to use, #Temp Table or @Table Variable?
-
I usually use #table in y stored proc. Which is the best to use in SP, #table or @Table variable?
Thanks
Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎 -
The below article by Wayne Sheffield might be of help to you
Comparing Table Variables with Temporary Tables
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
I'll keep it short. It depends. How many rows of data are you putting in the #tmp or @Tmp? How are you using #tmp or @Tmp.
The best thing to do is test, test, and test again. Be sure to stress test the procedure. Test against 1,000,000 rows of data when possible. You want to know that your process is scalable.
-
Also remember that recompile might play a factor. It's reduced or eliminated with table variables, but it's something you'll have to deal with when using temporary tables. Remember, there are principally only one difference between them, temporary tables have statistics. That difference should determine which you use in any given situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Depending on what your doing what about using a CTE?
***The first step is always the hardest *******
-
Jigneshal (7/20/2012)
I usually use #table in y stored proc. Which is the best to use in SP, #table or @Table variable?Thanks
If you are using this for small set of data, then I would go with @temp, otherwise its better to use #temp.
Regard,
TA
Regards,
SQLisAwe5oMe. -
SQLCrazyCertified (7/20/2012)
Jigneshal (7/20/2012)
I usually use #table in y stored proc. Which is the best to use in SP, #table or @Table variable?Thanks
If you are using this for small set of data, then I would go with @temp, otherwise its better to use #temp.
Regard,
TA
Thanks TA for your suggestion. I'll keep in mind. Thanks again.
Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎 -
SQLCrazyCertified (7/20/2012)
Jigneshal (7/20/2012)
I usually use #table in y stored proc. Which is the best to use in SP, #table or @Table variable?Thanks
If you are using this for small set of data, then I would go with @temp, otherwise its better to use #temp.
Regard,
TA
I would still try both because it truly "Depends".
As a side bar, the only place where I might use a Table Variable is in a UDF or when a scope change might keep a Temp Table from working properly because they're Temp Tables are usually easier to troubleshoot in SSMS since they persist in SSMS and Table Variables don't.
--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) -
Jeff Moden (7/21/2012)
SQLCrazyCertified (7/20/2012)
Jigneshal (7/20/2012)
I usually use #table in y stored proc. Which is the best to use in SP, #table or @Table variable?Thanks
If you are using this for small set of data, then I would go with @temp, otherwise its better to use #temp.
Regard,
TA
I would still try both because it truly "Depends".
As Jeff says it truly "Depends - try reading this:
http://blogs.msdn.com/b/sqlcat/archive/2008/10/09/table-variable-vs-temp-table-and-estimated-vs-actual-query-plan.aspx">"> http://blogs.msdn.com/b/sqlcat/archive/2008/10/09/table-variable-vs-temp-table-and-estimated-vs-actual-query-plan.aspx">
-
Kingston Dhasian (7/20/2012)
The below article by Wayne Sheffield might be of help to youComparing Table Variables with Temporary Tables
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
+1. Good reference
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Another very good reference of the differences between temp tables and table variables is at:
Knowing which to use will depend upon knowing the differences between each.
-
Some time back I was also in the same state of mind, I referred the link below (short and nice explanation):
http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
Hope this helps
Lokesh
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
This depends on your use as table variable have scope to there batch only as temporary tables have scope to their calling procedure.
-
SGT_squeequal (7/20/2012)
Depending on what your doing what about using a CTE?CTE's are not replacements for temp tables or table variables. A CTE will be incorporated into the actual query and then optimized. It is possible to use a CTE and improve performance, but it is also possible that it will degrade performance.
Whether you use temp tables, table variables or a CTE really depends on so many different factors that the only way to determine which is going to be 'best' is to test each method and evaluate them.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
+1
I agree with Jeffrey, best way is to test, test and test. I know some people writing about in-consistent query plans when using table variables over temp tables. But this is not the case.
Try testing on a large result set - results vary case to case basis.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply