Join column with different collation issue

Question

Hello everyone,

I am using SQL Server 2005. I have two tables, and they are using different collations. It is not allowed to concatenate columns from tables with different collations, for example the following SQL is not allowed,

select table1column1 + table2column2 from ...

My question is, why concatenation of two columns from different collations is not allowed from database engine design perspective? I do not know why collation will impact results, the result is just concatenating strings -- should be simple enough and not dependent on collation...

thanks in advance, George

Answer 1

OK,

I think the answer is simply that designing a system to work with unicode is a lot harder than the ascii character set. You have to worry about more than the letters; if you had arabic and french then how to you do string a + string b as they read in differant directions (not kana compatible i think is the term)?

You have to add a new work to collation - coercibility - my understanding is that coercibility is supposed to handle the compatibility of strings in different collations. i.e. it should allow a database engine by having rules such that that Latin1_CI_AS should really be compatible with Latin1_CI_AI under certain circumstances.

If you have a copy of SQL 99 Spec; read up on the coercibility of collations which defines how strings can be manipulated between collations. MS have tried to define it here on MSDN though i am not sure they have gone far enough compared to the standards; maybe sql2010 will be better... As far as my reading on the subject has gone; it still holds on SQL03 standard too - i guess for forward computability. The google books entry for SQL 99 doesnt have the pages on coercibility and i cant find anything bettr than the MSDN explanation.