June 6, 2013 at 7:52 am
ggeier (6/6/2013)
tabinsc (6/6/2013)
2008R1 tells me:Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '='.
IIF isn't supported in 2008, it's new to 2012. That's why you're getting an error.
I figured as much; I just expected to see a different error. This error looks more like IIF is not the problem but the IIF syntax is. No big deal.
Tony
------------------------------------
Are you suggesting coconuts migrate?
June 6, 2013 at 7:54 am
Why is this question about IIF broken because of the case sensitivity issue?
What real code would ever use varchar(1)?
Was the extra space in the varchar(4) field supposed to be a trickery?
Is there a situation where this special notation of a CASE is better?
Is this feature implemented in a way that we'll later be talking about the price to pay for using this shortcut instead of writing out the case? Of course we shouldn't, but the QotD(s) around "IsNull vs Coalesce" seem to be evergreen... 🙂
June 6, 2013 at 8:55 am
Mike Dougherty-384281 (6/6/2013)
Why is this question about IIF broken because of the case sensitivity issue?What real code would ever use varchar(1)?
Was the extra space in the varchar(4) field supposed to be a trickery?
Is there a situation where this special notation of a CASE is better?
Is this feature implemented in a way that we'll later be talking about the price to pay for using this shortcut instead of writing out the case? Of course we shouldn't, but the QotD(s) around "IsNull vs Coalesce" seem to be evergreen... 🙂
The answer to these and other questions right here, on the next episode of QotD!
Seriously though, varchar(1) really doesn't make much sense to me, I guess char(1) would do the job perfectly and avoid the extra 2 byte overhead.
I think the extra space was indeed a trickery to lure people into assuming string comparison takes trailing spaces into consideration.
I haven't found any comparisons between IIF and CASE, but I'm guessing the performance must be very similar, and IIF is only useful to type a little less than a case expression, but only when you have one case possibility and one else possibility (unless you want to nest IIFs, which I think wouldn't make any sense).
As far as I know, difference between ISNULL and COALESCE rests solely on type definition (considering the first argument vs. type precedence across all arguments), not on performance. Seems like IIF and CASE handle this in the same way: the value returned is always converted to the most prevalent type across both (or all, when using CASE) values.
June 6, 2013 at 8:55 am
Yes the correct answer is: it depends on which collation was selected during the install. The default is Latin1-General case-insensitive.
June 6, 2013 at 8:56 am
Yes the correct answer is: it depends on which collation was selected during the install. The default is Latin1-General case-insensitive.
June 6, 2013 at 9:02 am
Poorly written question, so you can't be sure what you are testing. Question should have specified collation was case insensitive.
June 6, 2013 at 9:37 am
Agreed. The question should have specified collation (or at least whether or not the collation was case-sensitive) since that would change whether or not 'r' = 'R'.... 🙂
June 6, 2013 at 9:46 am
Thanks for a nice simple one, Ron!
June 6, 2013 at 9:50 am
Thanks for an easy one Ron....Personally I hate IIF... and wish that it would go away from apps... not get added to them. 🙂
June 6, 2013 at 10:28 am
The first string has a space after the letter, the second does not. Without a Trim, why are they equal? Case sensitivity is irrelevant. The two strings aren't equal.
June 6, 2013 at 1:25 pm
Joseph M. Morgan (6/6/2013)
The first string has a space after the letter, the second does not. Without a Trim, why are they equal? Case sensitivity is irrelevant. The two strings aren't equal.
Trailing blanks are trimmed before string comparison. This is documented in Books Online.
What I am wondering, though, is why the question explicitly introduces both lowercase and uppercase. I now had to take a 50% gamble as to the default collation used by the author's server. Why not either use the same case for both, or explicitly specify the collation?
June 6, 2013 at 11:54 pm
Easy one, thanks Ron.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 7, 2013 at 1:50 am
Primo Dang (6/6/2013)
Seriously though, varchar(1) really doesn't make much sense to me, I guess char(1) would do the job perfectly and avoid the extra 2 byte overhead.
I disagree with you: CHAR and VARCHAR are similar but not equal.
CHAR is always filled with space, VARCHAR not.
Assume the following sample, you'll have different result!
declare
@C char(1)=''
,@v varchar(1)=''
print replace('12345678901234567890','5',@c)
print replace('12345678901234567890','5',@v)
print datalength(@c)
print datalength(@v)
Output:
1234 678901234 67890
123467890123467890
1
0
Please, note the space character between 4 and 6!
🙂
June 7, 2013 at 2:11 am
Carlo Romagnano (6/7/2013)
Primo Dang (6/6/2013)
Seriously though, varchar(1) really doesn't make much sense to me, I guess char(1) would do the job perfectly and avoid the extra 2 byte overhead.I disagree with you: CHAR and VARCHAR are similar but not equal.
CHAR is always filled with space, VARCHAR not.
Assume the following sample, you'll have different result!
declare
@C char(1)=''
,@v varchar(1)=''
print replace('12345678901234567890','5',@c)
print replace('12345678901234567890','5',@v)
print datalength(@c)
print datalength(@v)
Output:
1234 678901234 67890
123467890123467890
1
0
Please, note the space character between 4 and 6!
🙂
Good point. Hadn't thought of that, thanks! =]
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply