Niagara Falls

  • Comments posted to this topic are about the item Niagara Falls

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Whopeee first one to select the correct answer. (not a guess).

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Excellent question...most intriguing. Thanks.

  • Very interesting question.

    I knew the behaviour with the windows collation (no sql_ prefix to the collation name) because that's the collation I've worked with more than any other (in fact it's the only collation I've worked with since varchar(max) existed). I didn't know the behaviour with the other collation, but there was only one option with "hang" in it so I picked that one.

    So now I've learned something - the other collation works better for this weird case.

    Tom

  • Even stranger, used NVARCHAR(max) and the behavior is completely different.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (7/31/2011)


    Even stranger, used NVARCHAR(max) and the behavior is completely different.

    I noticed that too, and it inspired me to search my code for any varchars and change them to nvarchars, just in case. Given the relatively small size of our database, there's little downside that I can see.

    I work in the US, so this didn't manifest itself until one of our Canadian customers reported that an import process from Access was taking a very long time. I couldn't reproduce the problem until I took the step of setting up a server with Canadian collation. Once I isolated that REPLACE() as the culprit, I knew I had a good QotD on my hands. 😉

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • ronmoses

    Have you reported this anomaly to Microsoft Connect?

    http://connect.microsoft.com/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Surprising to see so many comments on a Sunday 🙂

    M&M

  • Thank-you for the interesting question!

    It is strange that VARCHAR(MAX) would behave differently. Has this ever been reported to Connect?

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • This article might be interesting: note the reply from microsoft at the bottom

    Replace of char(0) does not work in DB with Windows collation

    I know it isn't exactly the same issue, but it runs along the same lines...

  • I liked the QOTD and especially the Niagara Falls idea.

    Best Regards,

    Chris Büttner

  • David in .AU (7/31/2011)


    This article might be interesting: note the reply from microsoft at the bottom

    Replace of char(0) does not work in DB with Windows collation

    I know it isn't exactly the same issue, but it runs along the same lines...

    That explains it nicely - and Peter Larsson (SwePeso) added pretty much the exact same example as this QotD in the comments.

  • You got me on this one. I had no idea it would hang. Great question.

  • SQLkiwi (8/1/2011)

    That explains it nicely - and Peter Larsson (SwePeso) added pretty much the exact same example as this QotD in the comments.

    It's still realistically a bug, though--even if CHAR(0) is treated as an empty string in a Windows collation, REPLACE() given an empty string as its second parameter should do nothing, not hang the connection!

  • paul.knibbs (8/1/2011)


    SQLkiwi (8/1/2011)

    That explains it nicely - and Peter Larsson (SwePeso) added pretty much the exact same example as this QotD in the comments.

    It's still realistically a bug, though--even if CHAR(0) is treated as an empty string in a Windows collation, REPLACE() given an empty string as its second parameter should do nothing, not hang the connection!

    I'd also second that this is a bug, because the following works nicely:

    select replace(cast('Hello World' collate SQL_Latin1_General_CP1_CI_AS as varchar(50)), char(0), '')

    select replace(cast('Hello World' collate Latin1_General_100_CI_AS as varchar(50)), char(0), '')

    select replace(cast('Hello World' collate Latin1_General_CI_AS as varchar(50)), char(0), '')

    I'm well aware that the question was about varchar(max) (that's why I got it wrong)... but given only 11 characters tried it anyway.

    BTW, collation Latin1_General_100_ is English (Canada) (and many others) in SQL Server 2008, while Latin1_General_ is English (Canada) for SQL Server 2000 and 2005; you may want to check out this document.

    -Michael

Viewing 15 posts - 1 through 15 (of 64 total)

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