IFF - 1

  • sequelgarrett

    Right there with Babe

    Points: 783

    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.

  • tabinsc

    SSCommitted

    Points: 1812

    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?

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

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

  • Primo Dang

    SSCrazy

    Points: 2643

    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.

  • carlje54

    SSC Enthusiast

    Points: 180

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

  • carlje54

    SSC Enthusiast

    Points: 180

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

  • vsolomon

    SSC-Addicted

    Points: 407

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

  • LadyRuna

    SSCrazy

    Points: 2151

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

  • Revenant

    SSC-Forever

    Points: 42467

    Thanks for a nice simple one, Ron!

  • mtassin

    SSC-Insane

    Points: 23096

    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]

  • Joseph M. Morgan

    SSC Journeyman

    Points: 90

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21811

    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!

    🙂

  • Primo Dang

    SSCrazy

    Points: 2643

    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