July 30, 2011 at 2:35 pm
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
July 30, 2011 at 9:16 pm
July 31, 2011 at 12:07 am
Excellent question...most intriguing. Thanks.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 31, 2011 at 3:36 am
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
July 31, 2011 at 6:46 am
July 31, 2011 at 7:44 am
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
July 31, 2011 at 9:02 am
ronmoses
Have you reported this anomaly to Microsoft Connect?
July 31, 2011 at 11:10 am
Surprising to see so many comments on a Sunday 🙂
M&M
July 31, 2011 at 10:57 pm
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
July 31, 2011 at 11:57 pm
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...
August 1, 2011 at 12:58 am
I liked the QOTD and especially the Niagara Falls idea.
Best Regards,
Chris Büttner
August 1, 2011 at 1:05 am
David in .AU (7/31/2011)
This article might be interesting: note the reply from microsoft at the bottomReplace 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
August 1, 2011 at 3:19 am
You got me on this one. I had no idea it would hang. Great question.
August 1, 2011 at 3:42 am
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!
August 1, 2011 at 5:32 am
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 63 total)
You must be logged in to reply to this topic. Login to reply