IFF - 1

  • tabinsc (6/6/2013)


    2008R1 tells me:

    Msg 102, Level 15, State 1, Line 9

    Incorrect syntax near '='.

    It doesn't appear to like the @a = @B.

    IIF isn't supported in 2008, it's new to 2012. That's why you're getting an error.

  • ggeier (6/6/2013)


    tabinsc (6/6/2013)


    2008R1 tells me:

    Msg 102, Level 15, State 1, Line 9

    Incorrect syntax near '='.

    It doesn't appear to like the @a = @B.

    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?

  • 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... 🙂

  • 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.

  • Yes the correct answer is: it depends on which collation was selected during the install. The default is Latin1-General case-insensitive.

  • Yes the correct answer is: it depends on which collation was selected during the install. The default is Latin1-General case-insensitive.

  • Poorly written question, so you can't be sure what you are testing. Question should have specified collation was case insensitive.

  • 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'.... 🙂

  • Thanks for a nice simple one, Ron!

  • Thanks for an easy one Ron....Personally I hate IIF... and wish that it would go away from apps... not get added to them. 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • 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.

  • 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?


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • 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!

    🙂

  • 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 46 total)

You must be logged in to reply to this topic. Login to reply