June 23, 2008 at 10:53 am
sl.sajeev (6/23/2008)
Yes you are correct, There is something wrong with the choices i gave.
I bow to you because you demonstrate courage by submitting a QotD.
June 23, 2008 at 11:45 am
I learned something new today!!! I am so ingrained with VBA that I missed the T-SQL version of ISNULL. But why use ISNULL when there is a perfectly good SQL standard COALESCE function? Oracle does the same thing with NVL.
Actually, I am still having difficulty understanding the distinction. According to the BOL in the COALESCE topic:
ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.
Whatheheck does THAT mean?
--
Please upgrade to .sig 2.0
June 23, 2008 at 11:56 am
knechod (6/23/2008)
Actually, I am still having difficulty understanding the distinction. According to the BOL in the COALESCE topic:ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.
Whatheheck does THAT mean?
You make a very good point regarding the use of COALESCE in preference to ISNULL.
To answer your question - this is really only important if you use either COALESCE or ISNULL in a cmoputed column or in a view and you want the column to be regarded as NOT NULL (for instance, because you want to include the computed column in the primary key). If you define a computed column as COALESCE(a,b), it will always be regarded as potentially NULL, even if b is a column that can't be NULL or even a constant. If you choose ISNULL(a,b), the column will be regarded as NOT NULL if b is NOT NULL.
There is one more difference between ISNULL(a,b) and COALESCE(a,b) - and that is that COALESCE(a,b) uses the standard rules of datatype preference to determine whether the result will have the datatype of a or that of b. ISNULL(a,b) will always return the datatype of a, even if b has a higher precedence. This can result in unexpected and very unwelcome conversion errors!
Resuming, the only situation where I would allow ISNULL is in a computed column that has to be regarded as NOT NULL. In all other situations, I prefer COALESCE over ISNULL.
June 23, 2008 at 12:14 pm
The right answer is #2 -ONLY-
The author of the question may be was drunk! (haha)
June 23, 2008 at 12:28 pm
it wasnt select all that work
only option 2 is correct
June 23, 2008 at 12:36 pm
only option 2 is the correct answer, option 3 does not give the results in the required format!
June 23, 2008 at 1:39 pm
Ummm... no. This answer is wrong. Sorting the date in descending order will move the nulls to the bottom, but it will not leave the rest of the dates in ascending order. The only way to accomplish what he wants is option #2.
Ed Leighton-Dick | Consultant | Microsoft Data Platform MVP | MCSE | PASS Regional Mentor
June 23, 2008 at 2:56 pm
There is only one correct answer as demonstrated by Tom Garth.
Why do we allow such questions with not enough clarity?
June 23, 2008 at 3:07 pm
A) The author has admitted the mistake and that option 2 is the only correct option.
B) I think enough people have hammered this point on this thread, and it would be best if others, insteading of posting more gripes just let it go. There are no prizes for the most correct answers or anything.
C) If you really think you can do better, step up to the plate and give it a shot. I haven't tried it yet, but you never know when I might.
D) I was going to answer just #2, but decided to hold off. Glad I did, and though I don't have a point for a correct answer on the QotD tab, I just got a point for this post. I'll take that.
😎
June 23, 2008 at 3:12 pm
I'm not so much hammering on the author (who did admit his mistake - several times), but it would be nice if we had a little better proofing of these questions before they're published. This isn't the first question that has been off when it went to press, and I'm sure it won't be the last. I don't really care so much about missing the point, but do care that there was a fundamental flaw in the question/answer.
June 23, 2008 at 3:23 pm
I've read somewhere along the way that coalesce is faster than isnull. Not sure where I read it - probably somewhere in the SQL Server Central forums though. I've tested it and did see a slight improvement for what it's worth!
Guess those red marks in grade school left a permanent scar for many 🙂
June 23, 2008 at 3:26 pm
I agree with Peter. There are always going to be errors. But many errors can be prevented if QODs are validated by an independent "tester".
I would like to see Red Gate invest some money here. Not good for the reputation if there are these many preventable errors. Not only is it wasting the time of the readers (ok, it is probably also my fault to view all these comments), but also building "wrong knowledge".
Best Regards,
Chris Büttner
June 23, 2008 at 3:32 pm
COALESCE is also ANSI compliant as well as handling something such as:
COALESCE(@var1, @var2, @var3, ..., @var#)
This will return the first non-NULL value in however many are passed in. Very useful to avoid lots of nested ISNULL statements or a long CASE statement. 🙂
-Pete
June 23, 2008 at 3:48 pm
And, if memory serves, this same topic has come up in other threads about "bad questions" and the reply I vaguely remember from Steve Jones was that there used to be volunteers who would vet the questions, but after a couple of weeks most of these individuals stopping helping.
This is a free site, and much of the knowledge you get from this site comes NOT from the QotD's, but from the various forums that it hosts and the members that participate, such as Jeff Moden, Grant Fritchey. GSquared, GilaMonster, and many others.
The QotD is sometimes valuable, sometimes just for fun. The discussions on some are even more valuable. And if you learn something from QotD, good. Sometimes what you learn is that not everyone can write questions well, but at least they are trying to contribute to the community. The only way to learn is by doing, and this individual stepped up and tried and deserves credit for that even if the answer turned out wrong. The next question he writes may be better, and I hope he tries again.
😎
June 23, 2008 at 4:17 pm
It would appear that the question is not worded correctly: "The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving". The part that says "with the employees with DateOfLeaving coming last" seems to mean that employess with an actual date of leaving, rather than one that is null, should come last. Am I missing something here?
Viewing 15 posts - 76 through 90 (of 138 total)
You must be logged in to reply to this topic. Login to reply