Extending collation support in SQL Server and Jet, Part 2.1 (is this on?)

by Michael S. Kaplan, published on 2005/09/25 03:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/09/25/473686.aspx

But really have not heard much of anything yet from readers. Is this something that just does not seem useful for developers? Or is everyone waiting for more code? Or am I just describing something in too confusing of a way?

Hi Michael - I certainly appreciate your efforts as I work at a company that develops bilingual English/Welsh web applications using .NET and SQL Server (2000 at the moment).

Sadly, I don't think we'll be using your solution for a Welsh collation when we move on to .NET Framework v2 and Yukon. There are several reasons, but the foremost are lead time, cost and an element of "why bother".

Sorting in Welsh is something we have to do in order to give functional parity between the two languages. We've tried as many solutions as we can come up with, including using sort keys and indexes with triggers. Ultimately this proved to be an imperfect solution that required every developer to get their head around the extra columns and a custom process to generate sort keys. In our case, the solution turned out to need considerable tweaking to the point where we would have spent far too much time on getting sorting working, and wouldn't have had enough remaining time to fulfil the other 99% of the project requirements! I realise .NET 2 and Yukon would simplify a lot of this, but once bitten, twice shy.

The cost element - well, I guess this ties in with the lead time. We finally got our hands on a Welsh ELK and LIP in late 2004 and I'm sure one of many reasons it came out then and not 5 years sooner was commercial opportunity. If Wales were a market pumped with money, I'm sure the locale would have come sooner. But it isn't, and that also means that the companies who need custom software applications don't have such a large budget. To that end, if it's not something we can do quickly and easily, many of our clients instruct us not to bother with Welsh sorting (a position they shouldn't be in, especially if the client is from the public sector and has to comply with the Welsh Language Act). They'd much rather we spent our time and their money fulfilling their core requirements.

And the "why bother" bit? There are two elements to it. I'm not actually a Welsh speaker myself, but for many that are it's a politicised choice: "I don't have to do all this work to sort in English, why should I have to in Welsh?" For those of us who are more concerned about the practicalities, it's a case of "why bother doing all this when I could just change the RDBMS?". Sadly, it's got to the point where we're considering ditching SQL Server for Mimer, where configuring a Welsh collation requires about 30 seconds' work.

I realise that this goes a long way outside your original posts, and as I say, I certainly appreciate the efforts you've gone to. But from this corner of ELKland, that's how it goes unfortunately.

I hear you Chris.

In truth, I am hoping in the back of my mind that someone from the SQL Server team or the SQLCLR team will get so fired up at this solution that they will work to build VS/SQLS wizards that do a lot of that "auto column adding" automatically, as a kickass sample.

In truth though, the Mimer solution is more than 30 seconds work for most languages, because it depends on accurate tailorin to the PUA, which is not such a common item out there (the CLDR provides the largest source of them but I have been told by many that it suffers from a lot of inaccuracies).