Best practices for joining tables of different collation property database

Question

Hi,

I am trying to join two tables of databases present on same server but with different collation property and getting following error:

What are the best practices to join tables of different databases present on same server but with different collation property?

Answer 1

As the other say, you need cast the collation on one side, so that SQL Server knows under which rules to perform the = test.

As for what is best practice, there are two things to consider:

1. Correctness. That is, by which rules do you want to run the join? One collation is case-insensitive, the other is binary, that is case-sensitive.
2. Performance. When you cast the collation of a column, any index on that column is rendered useless, since the organisation of the index depends on the collation. If one table is big and the other is small, it is better to cast the collation on the small table.

If these two points are in conflict, the first point wins, obviously.

Answer 2

Please refer this thread :

https://stackoverflow.com/questions/20544392/how-to-fix-a-collation-conflict-in-a-sql-server-query

Answer 3

CAST the collation

SELECT
FROM Table1 AS T1
     INNER JOIN
     Table2 AS T2
         ON T1.Column1 COLLATE Latin1_Gereral_100_BIN2_UTF 
          = T2.Column2 COLLATE Latin1_Gereral_100_BIN2_UTF