Table Variable :Doesn't care

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

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

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

  • these rollback type questions are my favourites...

    thanks 🙂

  • 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!

  • [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

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

  • 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

  • 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

  • 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

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

  • 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/

  • 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

  • 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

  • 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 32 total)

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