July 6, 2010 at 9:30 pm
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;-)
July 6, 2010 at 10:52 pm
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. 😉
July 6, 2010 at 11:26 pm
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.
July 6, 2010 at 11:49 pm
these rollback type questions are my favourites...
thanks 🙂
July 6, 2010 at 11:54 pm
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!
July 6, 2010 at 11:55 pm
[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
July 7, 2010 at 12:12 am
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)
July 7, 2010 at 12:45 am
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
July 7, 2010 at 12:48 am
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
July 7, 2010 at 1:24 am
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
July 7, 2010 at 1:38 am
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.
July 7, 2010 at 2:07 am
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.
July 7, 2010 at 2:41 am
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
July 7, 2010 at 3:51 am
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
July 7, 2010 at 4:16 am
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