August 20, 2009 at 12:09 am
Comments posted to this topic are about the item cast, convert and float!
August 20, 2009 at 1:03 am
Hi, the question has a misprint, probably intended:
select @d = cast(convert(varchar(2),convert(varchar(3),@c)) as float) * 0.5
select @d
August 20, 2009 at 5:19 am
Probably should use SET vs. SELECT. Think I saw that as being deprecated, but I might be wrong?
August 20, 2009 at 7:09 am
Scary part is over 30% (at the time of this post) got it wrong. Many QoDs are quite tricky but this one is very basic and really not SQL specific (data type conversion or casting is an ABC of any programming language).
Looks like a concept of code review or inspection is missing way too often...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
August 20, 2009 at 7:14 am
I think the 30% of us (me included) that got it wrong just need to make sure we get our morning caffiene before trying to answer the questions 😉
August 20, 2009 at 7:26 am
Mark Horninger (8/20/2009)
Probably should use SET vs. SELECT. Think I saw that as being deprecated, but I might be wrong?
I was on that in another discussion. It doesn't say "deprecated", it does say "we recommend you don't use it", and no one knew why. Is SQL Server 7.0 or earlier around anywhere so we could see whether at any time it was announced deprecated? Or did we already do that?
I put the wrong answer, 10.5, I missed that the calculation result was stored back to the int variable(!)
Two thoughts on these exercises generally:
1. Often you look at the puzzle code and go "But you wouldn't do it that way!" But 1a, that isn't the point. And, 1b, the way "you" would do it may be different from the way "that guy who wrote some stuff for us till we found out he was raised by squirrels from birth and not a real MSCE at all" would do it. Some people do write squirrelly code.
A co-worker just couldn't read a query of mine yesterday, so there is something wrong with at least one of us, him and/or me!
2. If you only go in on the questions you think you know, and skip the harder ones, it skews the scoreboard. But 2a, who cares. 😉
August 20, 2009 at 7:34 am
rja.carnegie (8/20/2009)
Mark Horninger (8/20/2009)
Probably should use SET vs. SELECT. Think I saw that as being deprecated, but I might be wrong?I was on that in another discussion. It doesn't say "deprecated", it does say "we recommend you don't use it", and no one knew why. Is SQL Server 7.0 or earlier around anywhere so we could see whether at any time it was announced deprecated? Or did we already do that?
I put the wrong answer, 10.5, I missed that the calculation result was stored back to the int variable(!)
Two thoughts on these exercises generally:
1. Often you look at the puzzle code and go "But you wouldn't do it that way!" But 1a, that isn't the point. And, 1b, the way "you" would do it may be different from the way "that guy who wrote some stuff for us till we found out he was raised by squirrels from birth and not a real MSCE at all" would do it. Some people do write squirrelly code.
A co-worker just couldn't read a query of mine yesterday, so there is something wrong with at least one of us, him and/or me!
2. If you only go in on the questions you think you know, and skip the harder ones, it skews the scoreboard. But 2a, who cares. 😉
HEre's what I found - Set Vs. Select
http://ryanfarley.com/blog/archive/2004/03/01/390.aspx
http://vyaskn.tripod.com/differences_between_set_and_select.htm
August 20, 2009 at 7:37 am
A co-worker just couldn't read a query of mine yesterday, so there is something wrong with at least one of us, him and/or me!
Maybe both of you are quite OK and the missing part is good comments in the code...
Sometimes I write some SQL code I can not figure out myself few weeks later if I do not comment it correctly.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
August 20, 2009 at 8:12 am
I just plain got it wrong because of the int @C, after focusing too much on confirming the 21 * 0.5 calculation. Good question in that it highlights rookie mistakes I shouldn't be making....
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
August 20, 2009 at 8:56 am
I also missed the assignment back to the int variable. Oops. It would have been an obvious answer if only I'd paid better attention.
SELECTing into a variable is not being deprecated. But there are a few things to know when deciding whether to use SET or SELECT:
1) SET is the ANSI standard for assigning variable values. So if you care about that, there's your answer.
2) When assigning a value from a query, SET will raise an error if multiple rows are returned. SELECT will simply assign the last returned value and go about its merry way.
3) If the query returns zero rows, SET will set the variable to NULL. SELECT will leave the variable set to its current value.
4) SELECT can assign values to multiple variables simultaneously. So if you have three variables that each need a value from a given table, you'd have to query the table three times using SET, and only once with SELECT. Obviously this would have performance benefits in certain scenarios.
The conventional wisdom, as far as my research indicates, is that SELECT is preferable when you need to set multiple variables from a single query. SET is preferable when setting a single variable, or if ANSI-compliance is a requirement.
Ron Moses
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 20, 2009 at 8:58 am
A pity that 11 wasn't one of the options - then we'd have had to decide whether casting 10.5 as an integer truncated or rounded (and I would probably have guessed wrong!)
August 20, 2009 at 9:07 am
I think the 30% of us (me included) that got it wrong just need to make sure we get our morning caffiene before trying to answer the questions [Wink]
Yea! That's right! I'm only five short sips into my coke this morning so my wrong answer way way didn't count! 😀
I'm guessing that the first @C declaration made it an implied int, which I didn't get right off. I thought that it would change once it was converted to a floating point integer. I've been out of this game for a while now. It's amazing on how much you start to lose ... or middle age is finally starting to play a small role.
August 20, 2009 at 10:31 am
Re SET and SELECT, is there a realistic scenario OTHER than capturing previous statement's @@ERROR and @@ROWCOUNT where a single statement must be used? I think it's the only one that I've wanted.
I hadn't thought of the trick on that Web page of converting them to strings and concatenating them, to get both values captured by a SET statement. Effective - but ugly.
I suppose there isn't much point asking whether ANSI requires that each SET statement resets @@ROWCOUNT to 1 after execution - whether that's an ANSI rule or not, we are stuck with it. (Or are we?)
August 20, 2009 at 10:45 am
Jim in Arizona (8/20/2009)
I think the 30% of us (me included) that got it wrong just need to make sure we get our morning caffiene before trying to answer the questions [Wink]
Yea! That's right! I'm only five short sips into my coke this morning so my wrong answer way way didn't count! 😀
I'm guessing that the first @C declaration made it an implied int, which I didn't get right off. I thought that it would change once it was converted to a floating point integer. I've been out of this game for a while now. It's amazing on how much you start to lose ... or middle age is finally starting to play a small role.
A floating point integer? I think you might need a stronger drink, Jim... 😛
August 20, 2009 at 11:27 am
A floating point integer? I think you might need a stronger drink, Jim... [Tongue]
Yea, it's not my day, let me tell ya. It sounded good though. 🙂
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply