Database Collation problem when comparing two databases

Question

I have changed the collation of test database using the follwoing query:

USE master; 
GO 
ALTER DATABASE test 
COLLATE SQL_Latin1_General_CP1_CI_AI ; 
GO 
--Verify the collation setting. 
SELECT name, collation_name 
FROM sys.databases 
WHERE name = N'test'; 
GO 

Now when I want to compare a columns of test database against test2 database, I receive the following error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I have checked that both databases has SQL_Latin1_General_CP1_CI_AI collation in :

Management studio > Database > properties > options > collation. 

Is there another option rather than the database itself (e.g. schema) which still holds the old collation?

Answer 1

First: Collations for columns are stored per-each column.

Second: The ALTER DATABASE ... COLLATE ... statement changes only the default collation of the database itself. It does not change the collation of any existing columns within the database. This change affects:

• database-level meta-data (mostly names of database-level objects: tables, columns, procedures, functions, triggers, indexes, users, views, constraints, etc, etc) except in Contained databases (where the DB-level meta-data is always Latin1_General_100_CI_AS_KS_WS_SC)
• new string columns that do not specify a COLLATE clause. New via:
  • CREATE TABLE ...
  • ALTER TABLE ... ADD
  • DECLARE @TableVariable TABLE ... (these default to the database's collation, not [tempdb]'s collation)
• altered string columns that do not specify a COLLATE clause (i.e. ALTER TABLE ... ALTER COLUMN ...)
• new or altered columns that do specify COLLATE using DATABASE_DEFAULT
• string literals and variables

For an in-depth look at changing database-level (and even instance-level) collations, please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

For more info on working with collations in general, please visit: Collations Info

Answer 2

Check the collation on the columns you are comparing, you'll probably find out that on test the columns are still in SQL_Latin1_General_CP1_CI_AS. Try declaring collation explicitly in your comparison and see if it helps.

Also, are you storing something in tempdb? If your instance is based on one or the other you could have this issue.