Niagara Falls

Question

Post reply

Niagara Falls

ronmoses@gmail.com

SSCarpal Tunnel

Points: 4480
More actions
July 30, 2011 at 2:35 pm

#254497

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

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

Paul White SSC Guru Points: 150467 More actions · Answer 2

Excellent question...most intriguing. Thanks.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

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]

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 5

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

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]

M&M SSC-Insane Points: 21700 More actions · Answer 7

Surprising to see so many comments on a Sunday 🙂

M&M

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 8

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

David in .AU SSCommitted Points: 1784 More actions · Answer 9

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 10

I liked the QOTD and especially the Niagara Falls idea.

Best Regards,

Chris Büttner

Paul White SSC Guru Points: 150467 More actions · Answer 11

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

cengland0 SSCertifiable Points: 6102 More actions · Answer 12

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

paul.knibbs SSCoach Points: 15320 More actions · Answer 13

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!

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 14

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