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

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 appreci开发者_如何学Pythonated.

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.

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.