Best practice for joining tables of different warehouses with different collation

[PriyaJha]

Hi,

I am trying to join tables of different warehouses present on same server but with different collation property and getting the follwoing error:

What are the best practices to perform join between tables present in different warehouses but same server with different collation property?

[v-pnaroju-msft]

Hi PriyaJha,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Hi @PriyaJha,

Thank you for reaching out through the Microsoft Fabric Community Forum.

The error message indicates that there are two different collations in use. It is important to make them the same before performing a JOIN operation. This error occurs because the server mandates that collations for string comparisons, such as those in joins, must be identical.

Below are some best practices to resolve this issue:

1. Choose one of the existing collations or create a new one that suits both tables.

             Latin1_General_CI_AS – Case-insensitive and accent-sensitive.
             Latin1_General_BIN – Binary collation that treats strings based on byte values.

2. Modify the SQL query to use the COLLATE clause and explicitly specify the same collation for both columns in the JOIN condition:

   SELECT * FROM Table1 t1  JOIN Table2 t2  ON t1.ColumnName COLLATE  Latin1_General_CI_AS  = t2.ColumnName COLLATE  Latin1_General_CI_AS;

3. If you need to permanently change the collation for a specific column, run the following query:
   

   ALTER TABLE Table1

   ALTER COLUMN ColumnName VARCHAR(255) COLLATE Latin1_General_CI_AS;
   This action may affect existing data and other related operations, so be careful while proceeding.

4. Run the adjusted query to verify that the collation conflict has been resolved and that the JOIN operation executes successfully. Since collation changes can affect performance, it is advisable to test queries to ensure performance remains optimal after making these modifications.
   
   

   Also, please check these links for more information:

   COLLATE (Transact-SQL) - SQL Server | Microsoft Learn
   Collation and Unicode Support - SQL Server | Microsoft Learn
   
   

   If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
   
   Thank you.

   
   

[NandanHegde · Accepted solution]

Please refer this thread :

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