by Michael S. Kaplan, published on 2005/09/18 16:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/09/18/470869.aspx
Prior posts in the series:
Extending collation support in SQL Server and Jet, Part 0 (HISTORY)
Extending collation support in SQL Server and Jet, Part 1 (the broad strokes)
Okay, let's dig into some details, now. :-)
If you look back to June when I talked about how string.Compare is for sissies (not for people who want SQLCLR consistency) and July when I went a bit further and talked about how real developers use CompareInfo's Compare (Part 1), I really did spend some time talking about how if you want configurable collation in .NET, you have to use the CompareInfo object. Certainly for sort keys, there is no way other than the CompareInfo.GetSortKey method....
Now the first point is how many of the workarounds I discussed to move collation settings between Win32 and .NET or .NET and SQL Server are needed:
NONE.
That is right, you don't need to do any of those workarounds. Because the method in .NET will do the work for you of mapping out of .NET and Win32 through the "Windows-only CultureInfo" support. Cool, no? :-)
You do have to decide what you want to do with the various settings you have available to you, basically the members of the CompareOptions enumeration:
For the sake of consistency with other SQL Server collations, I would recommend staying away from IgnoreSymbols and StringSort (though at this point some of you may be thinking about how this method could be used to customize the support for existing collations!).
We will start with our CustomSqlCollation class, which will take a culture name and the comparison options to use (I'll build on this class more, in the future):
sealed public class CustomSqlCollation {
// Private members
private CultureInfo m_cultureInfo;
private CompareInfo m_compareInfo;
private CompareOptions m_options;
private string m_name;
// Constructor
public CustomSqlCollation(string name, CompareOptions options) {
this.m_cultureInfo = new CultureInfo("mn-CN-Mong", false);
this.m_compareInfo = this.m_cultureInfo.CompareInfo;
this.m_options = options;
this.m_name = name;
}
// Method to return an index value
public byte[] GetSortKey(string input) {
return this.m_compreInfo.GetSortKey(input, this.m_options);
}
// Compare method -- note that the many other forms of
// CompareInfo.Compare can also be done here
public int Compare(string string1, string string2) {
return this.m_compareInfo.Compare(string1, string2, this.m_options);
}
}
So, lets say that you have decided you want to create a custom collation for one of the new locales in Vista added for the Chinese minority language support -- Mongolian. The name of this culture that you would use to create it is mn-CN-Mong and it will create a CultureInfo object's whose NativeName is something like
ᠮᠣᠩᠭᠤᠯ ᠬᠡᠯᠡ (ᠪᠦᠭᠦᠳᠡ ᠨᠠᠢᠷᠠᠮᠳᠠᠬᠤ ᠳᠤᠮᠳᠠᠳᠤ ᠠᠷᠠᠳ ᠣᠯᠣᠰ)
and whose EnglishName is something like
Mongolian (Mongolian, People's Republic of China)
(That native name will show up much more effectively in Vista, which has the font and rendering engine support!)
On the whole it would likely be best to not ignore any of the weights when the language in question does not have any of those chracteristics (case, non-spacing marks, different Kana types, or different widths). So none of those other flags should be passed in this scenario -- the weights may be leveraged for other purposes in the script.
But now any time you need to create a value for your index (either inserting a row or changing the value in the column), you can call CustomSqlCollation.GetSortKey() to get the value to use -- using an insert trigger and a update trigger to create or regenerate the index value, as needed. And you can sort using that index column at any time.
If anybody told me that SQL Server was going to give good Mongolian script support, I would have been really skeptical -- until I realized it would not be that hard to do the actual work!
Next time, I'll cover those triggers and how to write them, as well as filling out more of the methods of the CustomSqlCollation class....
This post brought to you by "ᠯ" (U+182f, a.k.a. MONGOLIAN LETTER LA)
referenced by