Table Variable :Doesn't care

Question

Post reply

Table Variable :Doesn't care

Viewing 15 posts - 16 through 30 (of 32 total)

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

thabang.mogano Newbie Points: 4 More actions · Answer 1

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?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

thabang.mogano (7/7/2010)
so, would using the #table be better than using the table variable?

Depends

Temp tables

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.

Table Variables

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.

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

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

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

tommyh (7/6/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. 😉
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.

Thanks Tommy. I didn't know that I can use alt+x to execute. Always done with F5.

Sure alt+x is closer.

SQL DBA.

Cliff Jones SSChampion Points: 10547 More actions · Answer 4

Duncan Pryde (7/7/2010)
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

I also find this usefull, thanks for sharing.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

hrvoje.piasevoli

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

Excellent bit of code ..... Hope you submit some QOD's they would be a real challenge and learning experience.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

JF1081 SSC Eights! Points: 982 More actions · Answer 6

tommyh (7/6/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. 😉
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.

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?

Jan Sorenson Mr or Mrs. 500 Points: 579 More actions · Answer 7

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:

Rose Bud Hall of Fame Points: 3147 More actions · Answer 8

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?

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 9

wware (7/7/2010)
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?

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.

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/

KevinC. SSCommitted Points: 1570 More actions · Answer 10

Excellent question. I learned something new from all the other posts, too. 🙂

webrunner SSC-Dedicated Points: 31715 More actions · Answer 11

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

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Thanks for the QOD

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events