Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Table Variables Expand / Collapse
Author
Message
Posted Thursday, January 31, 2008 11:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:08 PM
Points: 2,396, Visits: 18,069
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
Post #450105
Posted Thursday, January 31, 2008 12:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 PM
Points: 6,133, Visits: 8,398
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
Post #450122
Posted Thursday, January 31, 2008 12:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #450124
Posted Thursday, January 31, 2008 12:53 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, Visits: 433
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.
Post #450141
Posted Thursday, January 31, 2008 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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. :)

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
Post #450186
Posted Thursday, January 31, 2008 5:15 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:23 PM
Points: 400, Visits: 571
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.

Post #450243
Posted Thursday, January 31, 2008 7:00 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 8, 2009 6:13 PM
Points: 134, Visits: 44
I did the SAME THING... 'hmm,,, looks like two are wrong ... but which is MORE wrong...'
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!:D
Post #450266
Posted Friday, February 1, 2008 8:03 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 11:43 AM
Points: 242, Visits: 433
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. :)

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.
Post #450511
Posted Monday, February 4, 2008 9:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 26, 2009 5:45 AM
Points: 357, 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."



Post #451226
Posted Monday, April 28, 2008 1:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 7:24 AM
Points: 283, Visits: 115
I tested this & not satisfied the answer. Please provide with an valid example.
Post #491669
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse