Post reply

Which is Best to use, #Temp Table or @Table Variable?

  • July 20, 2012 at 12:23 am

    #278058

    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....! "
    😎

  • July 20, 2012 at 12:27 am

    #1515305

    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 Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 20, 2012 at 12:29 am

    #1515306

    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.

  • July 20, 2012 at 4:58 am

    #1515426

    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 Roosevelt

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

  • July 20, 2012 at 5:31 am

    #1515451

    Depending on what your doing what about using a CTE?

    ***The first step is always the hardest *******

  • July 20, 2012 at 10:57 am

    #1515658

    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.

  • July 20, 2012 at 9:54 pm

    #1515816

    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....! "
    😎

  • July 21, 2012 at 4:40 pm

    #1515897

    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)

  • July 21, 2012 at 7:06 pm

    #1515909

    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">

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 21, 2012 at 7:07 pm

    #1515910

    Kingston Dhasian (7/20/2012)

    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/

    +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

  • July 21, 2012 at 8:15 pm

    #1515917

    Another very good reference of the differences between temp tables and table variables is at:

    http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server

    Knowing which to use will depend upon knowing the differences between each.

  • July 21, 2012 at 9:31 pm

    #1515920

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • July 22, 2012 at 9:21 am

    #1515984

    This depends on your use as table variable have scope to there batch only as temporary tables have scope to their calling procedure.

  • July 22, 2012 at 10:12 am

    #1515987

    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

  • July 22, 2012 at 10:44 am

    #1515991

    +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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

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

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