July 7, 2010 at 4:57 am
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?
July 7, 2010 at 6:03 am
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;-)
July 7, 2010 at 7:49 am
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.
July 7, 2010 at 8:09 am
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.
July 7, 2010 at 8:23 am
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.
July 7, 2010 at 9:06 am
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?
July 7, 2010 at 9:22 am
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:
July 7, 2010 at 9:35 am
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?
July 7, 2010 at 9:58 am
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.
July 7, 2010 at 11:53 am
Excellent question. I learned something new from all the other posts, too. 🙂
July 7, 2010 at 12:55 pm
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
July 7, 2010 at 2:53 pm
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
July 8, 2010 at 1:14 am
Thank you:-P
July 8, 2010 at 1:19 am
good question. Got the answer from the title
July 12, 2010 at 12:48 am
Easy!
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply