Table Variable :Doesn't care

  • Bhuvnesh

    SSC Guru

    Points: 59344

    Comments posted to this topic are about the item Table Variable :Doesn't care

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

  • UMG Developer

    SSChampion

    Points: 13482

    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. 😉

  • tommyh

    SSCertifiable

    Points: 6222

    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.

  • ziangij

    SSCertifiable

    Points: 6521

    these rollback type questions are my favourites...

    thanks 🙂

  • Adam-424116

    Ten Centuries

    Points: 1240

    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. 😉

    I didn't execute the code, but I was definitely helped by the title, "Table Variable: Doesn't Care"... All the same, it's a good question that's pointing out interesting and completely reasonable behavior that I never paid attention to before!

  • Kari Suresh

    Hall of Fame

    Points: 3712

    [p]Good point, covered nicely. I like it.:smooooth:[/p]

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    I didn't execute the code, but I was definitely helped by the title, "Table Variable: Doesn't Care"... All the same, it's a good question that's pointing out interesting and completely reasonable behavior that I never paid attention to before!

    It's true.I also find the answer using question it self witout executing script.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Koen Verbeeck

    SSC Guru

    Points: 258859

    Nice question to point out the difference between temp table and table variable, but a little more explanation would be helpful (such as a link to a BOL article).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    Nice question, thanks.

    According to Books Online, "Transactions involving table variables last only for the duration of an update on the table variable." - Good to know! One of those things that makes sense when you think about it, but could easily catch you out.

    Duncan

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    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

  • rohitvermasrt

    Grasshopper

    Points: 14

    Hey I m here for first time. And really its a nice question. And i also have given this answer by copying it and executing it in the query analyzer.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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. 😉

    I doubt it. Most people consider that cheating, and try to work the answer out for themselves.

    And I think that it is quite well-known that table variables (like scalar variables) are not affected by rollbacks.


    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258859

    Hugo Kornelis (7/7/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. 😉

    I doubt it. Most people consider that cheating, and try to work the answer out for themselves.

    Quite ironic that you post this comment right under someone who says he executed the query in Query Analyzer. 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • hrvoje.piasevoli

    Ten Centuries

    Points: 1377

    BOL: Transactions involving table variables last only for the duration of an update on the table variable. Therefore...

    Hi all!

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

    Try guessing the outcome...

    SET NOCOUNT ON

    GO

    DECLARE @t TABLE (id_char VARCHAR(20))

    CREATE TABLE #t (id INT)

    BEGIN TRY

    BEGIN TRAN

    -- insert 1 into temp table and output to table var

    INSERT #t

    OUTPUT INSERTED.*

    INTO @t

    SELECT 1

    -- Now insert 'A' into table var and output to temp table

    -- NOTE: 'A' can not be inserted into #t !

    INSERT INTO @t

    OUTPUT INSERTED.*

    INTO #t

    SELECT 'A'

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    IF XACT_STATE() >0 BEGIN

    PRINT 'COMMIT transaction'

    PRINT ERROR_MESSAGE()

    COMMIT TRAN

    END

    ELSE BEGIN

    IF XACT_STATE() < 0 BEGIN

    PRINT 'ROLLBACK transaction'

    ROLLBACK

    END

    PRINT ERROR_MESSAGE()

    END

    END CATCH

    -- display unioned results

    SELECT '@t' AS 'table_name', COUNT(*) AS 'row_count' FROM @t

    UNION ALL

    SELECT '#t' AS 'table_name', COUNT(*) AS 'row_count' FROM #t

    -- clean up

    DROP TABLE #t

    Regards,

    Hrvoje Piasevoli

    Hrvoje Piasevoli

  • hrvoje.piasevoli

    Ten Centuries

    Points: 1377

    Hugo Kornelis (7/7/2010)


    I doubt it. Most people consider that cheating, and try to work the answer out for themselves.

    I second that. Personally, giving the wrong answer highly motivates me to research, absorb information, learn and remember. And, after all, it's a safe environment for making errors:-P

    Best regards,

    Hrvoje Piasevoli

    Hrvoje Piasevoli

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

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