Table Variable :Doesn't care

  • Nice one 🙂

    i did fall for the copy, paste, F5 syndrome.

    but, it didn't point out something interesting.

    so, would using the #table be better than using the table variable?

  • thabang.mogano (7/7/2010)


    so, would using the #table be better than using the table variable?

    Depends

    Temp tables

    Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that proc calls.

    Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table.

    Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

    Table Variables

    These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.

    Like with temp tables, table variables reside in TempDB. they have entries in the system tables in tempDB, just like temp tables, and they follow the same behaviour regarding whether they are in memory or on disk.

    Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimiser has no way of knowing the number of rows in the table variable.

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

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • tommyh (7/6/2010)


    UMG Developer (7/6/2010)


    Nice question, thanks!

    I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome. 😉

    Or copy, paste, alt+x syndrome (F5 is to far away on the keyboard... and actually clicking execute is WAY out of the question).

    Good question though.

    Thanks Tommy. I didn't know that I can use alt+x to execute. Always done with F5.

    Sure alt+x is closer.

    SQL DBA.

  • Duncan Pryde (7/7/2010)


    Of course, one place this behaviour comes in useful is if you want to store the values from an output clause when rolling back a transaction. Just store them in a table variable first so they don't get lost when the transaction is rolled back.

    Have a look here at Martin Bell's explanation, which I found useful.

    Duncan

    I also find this usefull, thanks for sharing.

  • hrvoje.piasevoli

    Maybe the following code example brings some light on the above statement. Note the second insert in the try block.

    Try guessing the outcome

    Excellent bit of code ..... Hope you submit some QOD's they would be a real challenge and learning experience.

    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]

  • tommyh (7/6/2010)


    UMG Developer (7/6/2010)


    Nice question, thanks!

    I'm a little surprised that so many people have gotten it correct so far, but that may be because of the copy, paste, F5 syndrome. 😉

    Or copy, paste, alt+x syndrome (F5 is to far away on the keyboard... and actually clicking execute is WAY out of the question).

    Good question though.

    I have been wondering what the keyboard shortcut is for execute is. I learned 2 things today.

    By the way, this is not that hard to figure out without a copy and paste. I figured that 1,2 for both queries was too obvious; then I realized that one table is a variable so it probably would not be affected by the rollback. I guessed right.

    And I am assuming that rolling back a transaction does not change any type of variable to the pre-transaction value?

  • I like these kind of QOD because they point out the subtle differences in very similar things. After reviewing the post, I have been schooled and am better for it. Regarding the F5 cheating issue, I guess it's a matter of perspective on what game you are playing. For me, I guess I am not playing the traditional game but have engaged in another way of learning, I am just monitoring activity, and learning a lot along the way. :hehe:

  • Good QotD: Brief, straightforward illustration of something that may be well-known but was new to me.

    I must be missing something. In Hrvoje's example, I understand why there are no rows in #t but I don't understand why the 'A' row isn't in @t. Is it because the output into #t fails?

  • wware (7/7/2010)


    I must be missing something. In Hrvoje's example, I understand why there are no rows in #t but I don't understand why the 'A' row isn't in @t. Is it because the output into #t fails?

    That is because the statement fails. If a statement fails, it has no effect. Not on tables, nor on variables - and hence, not on table variables eiter.

    "SELECT @StringVar = 'a', @IntVar = 'a'" would leave both variables unaffected as well.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Excellent question. I learned something new from all the other posts, too. 🙂

  • Good question. Got it wrong, and now I know why.

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Thanks for the QOD

    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

  • Thank you:-P

  • good question. Got the answer from the title

  • Easy!

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

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