by Michael S. Kaplan, published on 2005/05/11 02:16 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/05/11/416293.aspx
A few minutes ago, I posted about Achieving case insensitivity, to deal with how best to make things case sensitive in an expression in SQL Server.
Now looking beyond the issue with that one expression, and to the wider issue of collations and what to use, for just a moment....
(This post contains several topics I will be covering in my TechEd 2005 talks in Orlando and Amsterdam -- lets hope TechEdBloggers picks it up, they missed this other one!)
Remember those old posts of mine (likeThey ask me "why is my Korean text in random order?" and A few of the gotchas of CompareString) where I implied (nicely) that calling CompareString with the NORM_IGNORENONSPACE flag is like making an official request of Windows to destroy important Korean data distinctions in collation? Well, an "accent insensitive" server is the SQL Server equivalent of that flag.
And of course all of the strange issues that the second post above point out about kana, width, and case insensitivity via NORM_IGNOREKANA, NORM_IGNOREWIDTH, and NORM_IGNORECASE? The ones that can potentially affect the Japan, Japan/Korea, and whole world in unanticipated ways, respectively? Those are the same as the respective collation types for SQL Server, too.
But let's ignore all that for a moment. Not everyone is working on international scenarios, and I am not quite naive enough to believe that they are. :-)
However, the truth is that you never know what the client is going to do with their server. And if you write an application that is fast and loose with the rules of case sensitivity in column names and it breaks because the customer installed it on their server which is case sensitive, you may be really unhappy with their reaction.
Even the Access team had a problem with this back in 1999 in their NorthwindCS.sql file that installed a SQL Server version of their NorthWind sample database -- of course they did manage to find that bug before too many people noticed it, and a lot of testers suddenly realized that case sensitivity is a veritable bug farm on a project that thinks it is ready to go, but has not yet tried it. :-)
I have talked to folks on the SQL Server test team in the past and have been told that more tests are run on case sensitive servers than not, for the same reason. In the end, installing things to not be insensitive to these distinctions will almost universally find more bugs than it hides. Even if your final production work will usually not be set up that way, a lot of the testing during the early phases will help shake out those bugs.
And that way if the client does run that way, you do not have to be embarrassed about the mixed use of case in your own queries and stored procedures!
This post brought to you by "₨" (U+20a8, a.k.a. RUPEE SIGN)
# Dean Harding on 11 May 2005 12:51 AM:
# Michael S. Kaplan on 11 May 2005 1:24 AM:
# Ambarish Sridharanarayanan on 11 May 2005 6:10 PM:
# Michael S. Kaplan on 15 May 2005 10:36 AM:
# Michael S. Kaplan on 16 May 2005 11:14 AM:
go to newer or older post, or back to index or month or day