SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variables


Table Variables

Author
Message
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4363 Visits: 18732
Thank you very much for your comments and your critique. I knew the question was difficult, and perhaps meant it to be somewhat tricky (using the transaction is valid, but does not perform as you might at first expect... unless you are Jim). However I did not intend for it to be confusing or unclear. I apologize if there was part that did not make sense on the first read. I hoped most people would take the SQL statements from the question and try to run them, similar to what Diana did. Using the exact statements provided, two will execute and two will not. I do appreciate the feedback (both above and hopefully below this post), and will incorporate your suggestions in future articles and QODs.

Thanks!
Chad
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18789 Visits: 12426
Hi Chad,

I'll just chime in here and say that I like the question very much. The only thing that could have imoproved it was if there had been an explicit statement "check all that pply" somewhere - now, I was in doubt and just tried if I could check more than one.

I had to try out one of the four options, because I had some doubts. The other three, I knew for sure. So I learned something today - and isn't that the aim of the QotD, rather than amassing points?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57617 Visits: 9730
I disagree with the point about the transactions.

Dictionary definition of "valid": "well based or logical" (Compact Oxford English Dictionary, as referenced on www.onelook.com).

Doing a transaction and rollback on a table variable is very much not well based or logical, since it does not do what one would probably expect. Since there are no other actions in the "transaction", the code makes no sense if one knows that it won't do anything.

If the intention of the question was, "Which of these will raise errors or fail to compile?", then the answers given would be correct, since it won't raise an error. But I certainly don't consider wrapping an insert into a table variable in a transaction to be "valid" code. If raising errors was the intent of the question, then it should have said that and avoided the word "valid".

(I feel like Inigo in "The Princess Bride": "I do not think that word means what you think it means.")

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
srienstr
srienstr
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 459
GSquared (1/31/2008)
I disagree with the point about the transactions.

Dictionary definition of "valid": "well based or logical" (Compact Oxford English Dictionary, as referenced on www.onelook.com).

Doing a transaction and rollback on a table variable is very much not well based or logical, since it does not do what one would probably expect. Since there are no other actions in the "transaction", the code makes no sense if one knows that it won't do anything.

If the intention of the question was, "Which of these will raise errors or fail to compile?", then the answers given would be correct, since it won't raise an error. But I certainly don't consider wrapping an insert into a table variable in a transaction to be "valid" code. If raising errors was the intent of the question, then it should have said that and avoided the word "valid".

(I feel like Inigo in "The Princess Bride": "I do not think that word means what you think it means.")

The term "invalid" has a specific contextual meaning in regard to SQL which is different from the standard dictionary definition, much as the definition of "character" in SQL is much more rigid than you will find in the dictionary and "integer" is much more limited than the definition in a mathematics textbook. His usage of "valid" is both accurate and precise within the framework in which he used it.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57617 Visits: 9730
srienstr (1/31/2008)
GSquared (1/31/2008)
I disagree with the point about the transactions.

Dictionary definition of "valid": "well based or logical" (Compact Oxford English Dictionary, as referenced on www.onelook.com).

Doing a transaction and rollback on a table variable is very much not well based or logical, since it does not do what one would probably expect. Since there are no other actions in the "transaction", the code makes no sense if one knows that it won't do anything.

If the intention of the question was, "Which of these will raise errors or fail to compile?", then the answers given would be correct, since it won't raise an error. But I certainly don't consider wrapping an insert into a table variable in a transaction to be "valid" code. If raising errors was the intent of the question, then it should have said that and avoided the word "valid".

(I feel like Inigo in "The Princess Bride": "I do not think that word means what you think it means.")

The term "invalid" has a specific contextual meaning in regard to SQL which is different from the standard dictionary definition, much as the definition of "character" in SQL is much more rigid than you will find in the dictionary and "integer" is much more limited than the definition in a mathematics textbook. His usage of "valid" is both accurate and precise within the framework in which he used it.


The only IT-specific definition of "valid" that I can find in any dictionary online is "A data-flow computer language", in which it is a proper noun.

If we're dealing with made up words here, with no formal/accepted definition, then I can validly say that your definition of "valid" is, in fact, invalid. Smile

Unless, of course, you can provide a source for that "specific contextual meaning". Can you?

(I do have actual dictionary definitions for the SQL use of words such as "integer", "character", "return", etc. Very easy to find those. "Valid", however, does not appear to have a specific SQL meaning, nor a meaning generic to anything related to error messages from code compilation/use.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Mike B in AK
Mike B in AK
Mr or Mrs. 500
Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)Mr or Mrs. 500 (533 reputation)

Group: General Forum Members
Points: 533 Visits: 634
While embedding an insert into a table variable in a transaction is valid and would function, the way it is in the question led me to check it, along with the two correct choices.

While I knew it would not fail if tested, it was a pointless option because it included extraneous code for inserting records into a table variable. If the question had inserts, updates or deletes into a temp or permanent table as part of the answer, I probably would not have checked it.

But, the end result is the statement would execute cleanly, so I will take my lumps and move on.
Coyote Blue
Coyote Blue
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 44
I did the SAME THING... 'hmm,,, looks like two are wrong ... but which is MORE wrong...' Pinch
And I am relatively new to tsql; only been doing 'hard' sql in my career for about 6 months (with no formal training); so I was *happy* I was 1/2 right!BigGrin
srienstr
srienstr
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 459
GSquared (1/31/2008)
The only IT-specific definition of "valid" that I can find in any dictionary online is "A data-flow computer language", in which it is a proper noun.

If we're dealing with made up words here, with no formal/accepted definition, then I can validly say that your definition of "valid" is, in fact, invalid. Smile

Unless, of course, you can provide a source for that "specific contextual meaning". Can you?

(I do have actual dictionary definitions for the SQL use of words such as "integer", "character", "return", etc. Very easy to find those. "Valid", however, does not appear to have a specific SQL meaning, nor a meaning generic to anything related to error messages from code compilation/use.)

These two sites show a similar definition to how my Computer Science professors used the term "valid code". They essentially state that valid code is code that meets the standards defined for the language, and thus will compile and run. This leaves the question of whether it did what you intended as a matter of how well it is coded.

Then again, I'm basing this on more general coding framework, so A) I was overly specific in my prior statement, and B) I was assuming that what I had learned as the general usage also applied to the SQL environment. Perhaps database programmers use different terminology; I haven't been involved in the SQL community long enough to know for sure. Had this been an application programming or web design forum, you'd be laughed at, but here I am the novice.

Whether I'm right or wrong, (the latter being more common, if you ask my wife) I thank you for helping me realize that I can't just blithely assume that terms I know from other coding I've done are being used in the same way here. It's more time consuming to verify one's understanding each time, but I prefer right to quick.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
JB-117464
JB-117464
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 58
Following on from Jim Fosters comment about the "BEGIN TRANSACTION; INSERT INTO @Vendors VALUES (1, 'Wicked Widgets Inc.', 'A');ROLLBACK TRANSACTION" statement. I ticked this as not being valid. This is clearly not the intended outcome and although it produces no syntax or runtime errors it should fall under the "NOT valid statements" category.

I would let it slide if there wasn't an identical statement without the rollback transaction which i think is designed to emphasise the difference. The only difference between the two is the rollback transaction which is not valid and should really give the following error "Msg 3903, Level 16, State 1, Line 1. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."



SG_Explorer
SG_Explorer
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 122
I tested this & not satisfied the answer. Please provide with an valid example.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search