Post reply

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

  • August 12, 2015 at 1:01 pm

    #1819488

    #temp is much more useful than @temp

  • August 12, 2015 at 3:29 pm

    #1819518

    caojunhe24 (8/12/2015)

    #temp is much more useful than @temp

    Care to share any of the reasons you think so? Without such a list, you comment is just an opinion.

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

  • August 13, 2015 at 5:13 am

    #1819609

    caojunhe24 (8/12/2015)

    #temp is much more useful than @temp

    Really? What about in the case of recompiles?

    "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

  • August 13, 2015 at 6:25 am

    #1819634

    Grant Fritchey (8/13/2015)

    caojunhe24 (8/12/2015)

    #temp is much more useful than @temp

    Really? What about in the case of recompiles?

    I heard a rumor that statistics can impact query performance as well. 😉

    My advice? Test, test and test some more.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 13, 2015 at 10:37 am

    #1819795

    Grant Fritchey (8/13/2015)

    caojunhe24 (8/12/2015)

    #temp is much more useful than @temp

    Really? What about in the case of recompiles?

    Or a user-defined table type?

    Better yet...

    A user defined memory optimized table type?

    What about in the case of a memory optimized table variable?

    Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.

    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

  • August 13, 2015 at 11:33 am

    #1819818

    SQLRNNR (8/13/2015)

    Grant Fritchey (8/13/2015)

    caojunhe24 (8/12/2015)

    #temp is much more useful than @temp

    Really? What about in the case of recompiles?

    Or a user-defined table type?

    Better yet...

    A user defined memory optimized table type?

    What about in the case of a memory optimized table variable?

    Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.

    Yeah, especially love the memory-optimized table. That's great.

    "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

  • August 13, 2015 at 12:14 pm

    #1819838

    Generally speaking:

    Table variables have scope limited to what one would expect with other types of variables, and it allow for fixed plans, which is a strong and compelling reason to stick with them when writing stored procedures for an OLTP scenario where data retreival and writes are low and maximum (sub-second) performance is required. Even the thing about use of table variable in DML operations resulting in non-parallel plans seems to fit well with an OLTP case usage.

    Unlike a table variable, you can SELECT.. INTO.. a temp table. Also it supports statistics and non-clustered indexes, which are strong and compelling reason to stick with temp tables for stored procedures which perform heavy duty ETL or reporting type processing.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 13, 2015 at 2:20 pm

    #1819895

    Eric M Russell (8/13/2015)

    Also it supports statistics and non-clustered indexes...

    Table variables can have nonclustered indexes. No stats, but they can have nonclustered indexes. You just have to define them in the initial table definition.

    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

  • August 13, 2015 at 2:44 pm

    #1819905

    SQLRNNR (8/13/2015)

    Grant Fritchey (8/13/2015)

    caojunhe24 (8/12/2015)

    #temp is much more useful than @temp

    Really? What about in the case of recompiles?

    Or a user-defined table type?

    Better yet...

    A user defined memory optimized table type?

    What about in the case of a memory optimized table variable?

    Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.

    Or perhaps an edge case where you might need to rollback a transaction but not have it affect the temp data. A table variable is excellent in that situation (although I have never needed that in the real world).

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 13, 2015 at 2:57 pm

    #1819913

    Sean Lange (8/13/2015)

    SQLRNNR (8/13/2015)

    Grant Fritchey (8/13/2015)

    caojunhe24 (8/12/2015)

    #temp is much more useful than @temp

    Really? What about in the case of recompiles?

    Or a user-defined table type?

    Better yet...

    A user defined memory optimized table type?

    What about in the case of a memory optimized table variable?

    Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.

    Or perhaps an edge case where you might need to rollback a transaction but not have it affect the temp data. A table variable is excellent in that situation (although I have never needed that in the real world).

    I wish that temp tables and table variables were simply non-transacted. I've never had a need to rollback a temp table, although there have been many occasions where I've hit the cancel button on a query that was dumping too much data into a temp table and filling tempdb and transaction log.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 14, 2015 at 6:23 am

    #1820020

    If you are manually running some code in SSMS, table variables have the additional advantage of being generated fresh on every run. Temp tables stay around, and you have to add code to either clear their contents, or delete and re-create them on every non-first run.

    If you are creating something that will reside in a stored procedure, the main difference is as Grant says, that temp tables have statistics, and so are more appropriate for operations where you will be doing operations that involve multiple references to the table, like joins. The query optimizer can use those statistics to generate better query plans for operations on the temp tables.

    Table variables are simple for short-term 'clipboard' use, where you maybe collect a subset of info, then do simple operations on that subset, like additional filtering, summing all results and such, where you need to scan the entire dataset anyway, and statistics won't speed up anything. In fact, they might slow the operation down, since creation of statistics takes some time. If they're never going to be used, it's pointless to generate them.

  • August 14, 2015 at 8:31 am

    #1820073

    GilaMonster (8/13/2015)

    Eric M Russell (8/13/2015)

    Also it supports statistics and non-clustered indexes...

    Table variables can have nonclustered indexes. No stats, but they can have nonclustered indexes. You just have to define them in the initial table definition.

    Table variables can have nonclustered indexes, but I thought they had to be tied to a PRIMARY KEY or UNIQUE constraint. Can you define a nonclustered index on a non-unique set of columns in a table variable?

  • August 14, 2015 at 8:44 am

    #1820079

    pdanes (8/14/2015)

    If you are manually running some code in SSMS, table variables have the additional advantage of being generated fresh on every run. Temp tables stay around, and you have to add code to either clear their contents, or delete and re-create them on every non-first run.

    That can also be a disadvantage if you're troubleshooting and need to see the content or it needs to persist so you can troubleshoot a section after the "table" creation without having to rerun all the preceding code.

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

  • August 14, 2015 at 8:49 am

    #1820084

    Eric M Russell (8/13/2015)

    I wish that temp tables and table variables were simply non-transacted.

    I'll strongly second that wish. I also wish that there were a way to mark certain things within transactions as exceptions that should not be rolled back to make on the fly logging a whole lot easier.

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

  • August 14, 2015 at 9:39 am

    #1820107

    Jeff Moden (8/14/2015)

    Eric M Russell (8/13/2015)

    I wish that temp tables and table variables were simply non-transacted.

    I'll strongly second that wish. I also wish that there were a way to mark certain things within transactions as exceptions that should not be rolled back to make on the fly logging a whole lot easier.

    Are you talking about support for autonomous transactions, perhaps similar to Oracle's AUTONOMOUS_TRANSACTION pragma?

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

    When it comes to things like ETL or heavy duty aggregate reporting; ACID can become an unnecessary overhead, and SQL Server could benefit from something like a SET AUTONOMOUS_TRANSACTION ON; option for use in stored procedures or ad-hoc querying.

    I recall a few years back about how to simulate that in SQL Server using a loopback linked server. However, I'm not sure I'd want to do down that path, unless it were for a one-off process that really needed it.

    http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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