SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bhuvnesh / Table Variable :Doesn't care / Latest Posts

RE: Table Variable :Doesn't care

zymos — Wed, 07 Mar 2012 15:26:04 GMT

Great question and I like that it touches the ROLLBACK concept.

RE: Table Variable :Doesn't care

Dhruvesh Shah — Sun, 07 Nov 2010 16:31:33 GMT

Thanks, didn't know that rollback didn't effect declared variables.

RE: Table Variable :Doesn't care

vinayak-365346 — Wed, 14 Jul 2010 03:27:44 GMT

Nice Question :)

RE: Table Variable :Doesn't care

VM-723206 — Mon, 12 Jul 2010 00:48:54 GMT

Easy!

RE: Table Variable :Doesn't care

Aparna-1 — Thu, 08 Jul 2010 01:19:41 GMT

good question. Got the answer from the title

RE: Table Variable :Doesn't care

girishbiswal-701083 — Thu, 08 Jul 2010 01:14:14 GMT

Thank you:-P

RE: Table Variable :Doesn't care

SQLRNNR — Wed, 07 Jul 2010 14:53:02 GMT

Thanks for the QOD

RE: Table Variable :Doesn't care

webrunner — Wed, 07 Jul 2010 12:55:50 GMT

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

RE: Table Variable :Doesn't care

KevinC. — Wed, 07 Jul 2010 11:53:47 GMT

Excellent question. I learned something new from all the other posts, too. :-)

RE: Table Variable :Doesn't care

Hugo Kornelis — Wed, 07 Jul 2010 09:58:35 GMT

[quote][b]wware (7/7/2010)[/b][hr]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?[/quote]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.

RE: Table Variable :Doesn't care

Rose Bud — Wed, 07 Jul 2010 09:35:08 GMT

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?

RE: Table Variable :Doesn't care

Jan Sorenson — Wed, 07 Jul 2010 09:22:14 GMT

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:

RE: Table Variable :Doesn't care

JF1081 — Wed, 07 Jul 2010 09:06:23 GMT

[quote][b]tommyh (7/6/2010)[/b][hr][quote][b]UMG Developer (7/6/2010)[/b][hr]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. ;-)[/quote]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.[/quote]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?

RE: Table Variable :Doesn't care

bitbucket-25253 — Wed, 07 Jul 2010 08:23:52 GMT

hrvoje.piasevoli [quote]Maybe the following code example brings some light on the above statement. Note the second insert in the try block.Try guessing the outcome[/quote]Excellent bit of code ..... Hope you submit some QOD's they would be a real challenge and learning experience.

RE: Table Variable :Doesn't care

Cliff Jones — Wed, 07 Jul 2010 08:09:54 GMT

[quote][b]Duncan Pryde (7/7/2010)[/b][hr]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 [url=http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/14/table-variables-and-output-clause.aspx]Martin Bell's explanation[/url], which I found useful.Duncan[/quote]I also find this usefull, thanks for sharing.

RE: Table Variable :Doesn't care

SanjayAttray — Wed, 07 Jul 2010 07:49:13 GMT

RE: Table Variable :Doesn't care

Bhuvnesh — Wed, 07 Jul 2010 06:03:02 GMT

[quote][b]thabang.mogano (7/7/2010)[/b][hr]so, would using the #table be better than using the table variable?[/quote]Depends [b]Temp tables[/b]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.[b]Table Variables[/b]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.[url]http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx[/url]

RE: Table Variable :Doesn't care

thabang.mogano — Wed, 07 Jul 2010 04:57:10 GMT

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?

RE: Table Variable :Doesn't care

hrvoje.piasevoli — Wed, 07 Jul 2010 04:16:46 GMT

[quote][b]Hugo Kornelis (7/7/2010)[/b][hr]I doubt it. Most people consider that cheating, and try to work the answer out for themselves.[/quote]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

RE: Table Variable :Doesn't care

hrvoje.piasevoli — Wed, 07 Jul 2010 03:51:10 GMT

[quote]BOL: Transactions involving table variables last only for the duration of an update on the table variable. Therefore...[/quote]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...[code="sql"]SET NOCOUNT ONGODECLARE @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 TRANEND TRYBEGIN 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() ENDEND CATCH-- display unioned resultsSELECT '@t' AS 'table_name', COUNT(*) AS 'row_count' FROM @tUNION ALLSELECT '#t' AS 'table_name', COUNT(*) AS 'row_count' FROM #t-- clean upDROP TABLE #t[/code][quote][/quote][quote][/quote]Regards,Hrvoje Piasevoli

RE: Table Variable :Doesn't care

Koen Verbeeck — Wed, 07 Jul 2010 02:41:46 GMT

[quote][b]Hugo Kornelis (7/7/2010)[/b][hr][quote][b]UMG Developer (7/6/2010)[/b][hr]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. ;-)[/quote]I doubt it. Most people consider that cheating, and try to work the answer out for themselves.[/quote]Quite ironic that you post this comment right under someone who says he executed the query in Query Analyzer. :-D

RE: Table Variable :Doesn't care

Hugo Kornelis — Wed, 07 Jul 2010 02:07:36 GMT

[quote][b]UMG Developer (7/6/2010)[/b][hr]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. ;-)[/quote]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.

RE: Table Variable :Doesn't care

rohitvermasrt — Wed, 07 Jul 2010 01:38:41 GMT

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.

RE: Table Variable :Doesn't care

Duncan Pryde — Wed, 07 Jul 2010 01:24:25 GMT

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 [url=http://sqlblogcasts.com/blogs/martinbell/archive/2009/06/14/table-variables-and-output-clause.aspx]Martin Bell's explanation[/url], which I found useful.Duncan

RE: Table Variable :Doesn't care

Duncan Pryde — Wed, 07 Jul 2010 00:48:24 GMT

Nice question, thanks.According to [url=http://msdn.microsoft.com/en-us/library/ms175010.aspx]Books Online[/url], "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

RE: Table Variable :Doesn't care

Koen Verbeeck — Wed, 07 Jul 2010 00:45:19 GMT

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

RE: Table Variable :Doesn't care

malleswarareddy_m — Wed, 07 Jul 2010 00:12:33 GMT

[quote]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![/quote]It's true.I also find the answer using question it self witout executing script.

RE: Table Variable :Doesn't care

Kari Suresh — Tue, 06 Jul 2010 23:55:08 GMT

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

RE: Table Variable :Doesn't care

Adam-424116 — Tue, 06 Jul 2010 23:54:39 GMT

[quote][b]UMG Developer (7/6/2010)[/b][hr]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. ;-)[/quote]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!

RE: Table Variable :Doesn't care

ziangij — Tue, 06 Jul 2010 23:49:00 GMT

these rollback type questions are my favourites...thanks :)

RE: Table Variable :Doesn't care

tommyh — Tue, 06 Jul 2010 23:26:11 GMT

[quote][b]UMG Developer (7/6/2010)[/b][hr]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. ;-)[/quote]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.

RE: Table Variable :Doesn't care

UMG Developer — Tue, 06 Jul 2010 22:52:29 GMT

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

Table Variable :Doesn't care

Bhuvnesh — Tue, 06 Jul 2010 21:30:37 GMT

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