by Michael S. Kaplan, published on 2005/06/16 10:25 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/06/16/429759.aspx
Fascinating post about Locales in SQL Server, one that went into much more detail than I did at TechEd last week. It is from the from the new SQLCLR Integration blog and is definitely worth a read or three...
They did a really good description of not only the problems in the old Microsoft C Runtime (CRT) locale model where anyone in the process could modify the locale and change the functionality of a broad range of settings, but of the very cool CRT solution in Whidbey -- functions where you can pass the locale you had set. And they top it off by saying that they pretty much try to keep the locale set to be the "C" locale (I have posted about that beast and its effect on the meaning of case insensitivity previously).
The interesting idea (in my opinion) is that they do not try to keep the CRT locale in sync with the SQL collation setting. Which in a way makes sense; after all, which would they choose to be similar to? The server's? Kind of useless and weird when the database's did not match. Better not to jump into a fight with a model when one cannot win it. :-)
It also talks about the CLR and its notion of locales (CurrentCulture and CurrentUICulture) and how they have a slightly different solution there. SQL Server leaves the CurrentCulture alone, so it is subject to similar issues as the old CRT locale, with one important difference and one very eerie similarity.
The difference is that developers who change it only affect the thread they are in, which is obviously not the same as that CRT-wide setting. The similarity is that they leave the CurrentCulture and CurrentUICulture alone, subject to the server's settings. Since these settings also may or may not match the SQL Server settings, one could run into all kinds of strange incompatibilities, even beyond the ones I talk about in my String.Compare and SQLCLR post (which has someow made it quickly to the top of lot of different web searches, and not because of its use of the word sissy; people really seem to care about SQLCLR and keeping the two sides of the partnership as compatible as possible!).
I probably would have made the push to try to make the SQL Server and the CLR locales be the same, especially given the issues with wanting similar comparisons, but I can understand that it does not affect everyone and since there is a 'tax' one has to pay to have them in sync in every single worker thread used by SQL Server, it really is unreasonable to make everyone pay that tax. Plus the same issuesof which collation would be best to use come up. So such a push would fail, with good reasons.
But in any individual usage of the CLR within SQL Server, there is the method to help you keep in sync with SQL Server's settings (whichever ones you like) -- which I talked about in Orlando and will talk about in Amsterdam -- a GetCultureInfo override that I will talk about more another day -- a great addition to the already cool feature Mike discusses. More on this another day....
Plus there is that String.Compare post that talks about for serious CLR work you probably want to be using CompareInfo.Compare anyway, for maximum SQL/CLR compatibility of collation results.
But this post by the SQLCLR folks gives some additional issues, as well as the need to keep the differences between all of these different technology models in mind. Very cool!
referenced by