Problems with the case and sorting when my SQL Server database is installed on Latin1_General_100_CI_AI

Question

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

Also, I get a folowwing message when joining two tables on nvarchar. Tables are also in the same sort.

Unable to resolve collision conflict between "Latin1_General_100_CI_AI" and "Latin1_General_CI_AS" in equal action.

Any help would be greatly appreciated.

Answer 1

Try entering one of the fields in another field mapping:

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 when creating a field, the predicate cannot compare with the index in this field.

Answer 2

Once you have already created the objects inside the database, simply changing the sort does not change the existing objects. You can see this by right-clicking on the tables and executing their scripts - they will have a lot of notes about varchar field mappings. If you want objects to change, you need to recreate them.