Table Variable :Doesn't care

Question

Table Variable :Doesn't care

Bhuvnesh

SSC Guru

Points: 59351
More actions
July 6, 2010 at 9:30 pm

#387284

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

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

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

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: 6302 More actions · Answer 2

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 SSCrazy Eights Points: 8066 More actions · Answer 3

these rollback type questions are my favourites...

thanks 🙂

Adam-424116 Ten Centuries Points: 1240 More actions · Answer 4

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 More actions · Answer 5

[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 More actions · Answer 6

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: 259115 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 64790 More actions · Answer 11

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: 259115 More actions · Answer 12

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 More actions · Answer 13

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 Ten Centuries Points: 1377 More actions · Answer 14

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