topic Re: Best practice for joining tables of different warehouses with different collation in Data Warehouse

Best practice for joining tables of different warehouses with different collation

PriyaJha — Thu, 30 Jan 2025 10:58:09 GMT

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?

Re: Best practice for joining tables of different warehouses with different collation

NandanHegde — Thu, 30 Jan 2025 11:01:10 GMT

Please refer this thread :

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

Re: Best practice for joining tables of different warehouses with different collation

v-pnaroju-msft — Fri, 31 Jan 2025 10:46:21 GMT

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:

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.
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;
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.
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.

Re: Best practice for joining tables of different warehouses with different collation

v-pnaroju-msft — Mon, 03 Feb 2025 20:11:27 GMT

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.