Issues with case and collation when my SQL Server database is set to Latin1_General_100_CI_AI

Question

My SQL Server 2008 database is set to Latin1_General_100_CI_AI collation, yet when I query the database using Management Studio it is still accent sensitive. What am I missing?

Also, I get the following message when joining two tables on a nvarchar. The tables are both on the same collation too.

Cannot resolve the collation conflict between "Latin1_General_100_CI_AI" and "Latin1_General_CI_AS" in the equal to operation.

Any help would be greatly appreciated.

Answer 1

Try casting one of the fields into the other field's collation:

SELECT  *
FROM    as_table
JOIN    ai_table
ON      ai_field = as_field COLLATE Latin1_General_100_CI_AI

or

SELECT  *
FROM    ai_table
JOIN    as_table
ON      as_field = ai_field COLLATE Latin1_General_100_CI_AS

Note that casting a field makes the predicate unsargable against the index on this field.

Answer 2

Once you've already created objects inside the database, simply changing the collation doesn't change the existing objects. You can see this by right-clicking the tables and scripting them out - they'll have lots of notes about the collations of the varchar fields. If you want the objects to change, you'll need to recreate them.