Share via

Best practices for joining tables of different collation property database

Priya Jha 901 Reputation points
2025-01-30T10:48:40.5633333+00:00

Hi,

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

CollationIssue

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

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
Answer accepted by question author
  1. Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator
    2025-01-30T22:40:37.0866667+00:00

    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.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 47,616 Reputation points
    2025-01-30T11:14:02.4566667+00:00

    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

    1 person found this answer helpful.
    0 comments
  2. Nandan Hegde 36,801 Reputation points MVP Volunteer Moderator
    2025-01-30T11:00:59.0333333+00:00

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.