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.