Forum Discussion
Super UserCOLLATION issue in Fabric Warehouse
I am loading two different ERP data into a fabric Lakehouse through ODBC connector. My end goal is to merge required columns from two different system table and make a centeral one, but i am experiencing issus deu to different collation.
Here is different stage collation breakdown
Source
| Location | Column | COLLATION_NAME |
| Source 1 | FirmID | NULL |
| Source 1 | FirmName | Latin1_General_100_BIN2_UTF8 |
| Source 2 | FirmID | Latin1_General_100_BIN2_UTF8 |
| Source 2 | FirmName | Latin1_General_100_BIN2_UTF8 |
So, here only difference is Source 1 "FirmID" has NULL collation.
Next stage,
When i created two different views for individual source so you could consider Source 1 become view1 and source 2 become view2.
After creating the view i got these collation
| Location | Column | COLLATION_NAME |
| View1 | FirmID | Latin1_General_100_CI_AS_KS_WS_SC_UTF8 |
| View1 | FirmName | Latin1_General_100_BIN2_UTF8 |
| View2 | FirmID | Latin1_General_100_BIN2_UTF8 |
| View2 | FirmName | Latin1_General_100_BIN2_UTF8 |
NULL collation become: Latin1_General_100_CI_AS_KS_WS_SC_UTF8
When i merge these two view
select
[Firm Id],
[Firm Name]
FROM source1.view1
union all
select
[Firm Id],
[Firm Name]
FROM source2.view2i got this error
| Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "Latin1_General_100_CI_AS_KS_WS_SC_UTF8" in the equal to operation |
Possible Fix:
select
[Firm Id],
[Firm Name]
FROM source1.view1
union all
select
[Firm Id] = [Firm Id] COLLATE Latin1_General_100_CI_AS_KS_WS_SC_UTF8,
[Firm Name] = [Firm Name] COLLATE Latin1_General_100_CI_AS_KS_WS_SC_UTF8
FROM source2.view2Note: I have checked , used lakehouse default collation is "SQL_Latin1_General_CP1_CI_AS"
Question 1: Why view is not capturing the default collation when the collation is NULL?
Question 2: How can i overcome this issue except applying COLLATE Latin1_General_100_CI_AS_KS_WS_SC_UTF8?
Thanks for your attention on this matter.
From what I’ve found, the best long-term solution is to define workspace-level collation settings. Microsoft Fabric now supports setting default collation at the workspace level, which new SQL endpoints and Warehouses can inherit automatically. This helps standardize collation across artifacts and reduces the need for manual overrides.
Additionally, using Materialized Lake Views might help. These views allow you to define transformations declaratively and can be used to enforce consistent schema and collation during ingestion.
Hi Royel ,
Thank you for reaching out to the Microsoft Community Forum.
Why view is not capturing the default collation when the collation is NULL?
Solution: When a column has NULL collation in the source, the SQL analytics endpoint in Fabric Lakehouse assigns a default collation during view creation. This default is not necessarily the workspace default (SQL_Latin1_General_CP1_CI_AS). Instead, it often defaults to Latin1_General_100_CI_AS_KS_WS_SC_UTF8, which is case-sensitive, even though the workspace shows a case-insensitive default.
Note: The SQL endpoint is case-sensitive by default, and unless explicitly overridden, columns may inherit different collations depending on their type and origin.
How can i overcome this issue except applying COLLATE Latin1_General_100_CI_AS_KS_WS_SC_UTF8?
Solution: There is no automatic way to apply a uniform collation to all existing Lakehouse views or tables.
Please try below alternative workarounds.
1. Use Fabric Warehouses, It allow you to set collation at creation time using the REST API. You can specify Latin1_General_100_CI_AS_KS_WS_SC_UTF8 as the default collation for the entire warehouse.2. Changing the workspace collation only affects newly created Lakehouse or Warehouse. Existing resources retain their original collation settings for backward compatibility.
3. If you want to work within Lakehouse, consider creating views with explicit COLLATE clauses for key columns. You can automate this using scripts or templates to reduce manual effort.
Note: Apply consistent collation during data load or transformation. Create intermediate views with unified collation before merging. Use scripting to apply COLLATE across multiple views/tables. Prefer Warehouses for complex joins/unions.
Please refer below links.
Data Warehouse collation - Microsoft Fabric | Microsoft Learn
Solved: Lakehouse SQL Endpoint (db) Collation - Microsoft Fabric Community
Is it possible to create Lakehouse with case insensitive collation in Fabric? - Microsoft Q&A
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
6 Replies
- anilgavhane
Royel Here are some practical alternatives:
Option 1: Standardize Collation During View Creation
Instead of applying COLLATE in every query, define the collation explicitly in the view itself:
CREATE VIEW source1.view1 AS SELECT CAST([FirmID] AS NVARCHAR) COLLATE Latin1_General_100_BIN2_UTF8 AS [FirmID], [FirmName] FROM source1.table
This ensures consistent collation across views and avoids conflicts during UNION, JOIN, or comparisons.
Option 2: Use a Staging Table with Unified Collation
Create a staging table in the Lakehouse with explicitly defined collation:
CREATE TABLE staging.FirmData ( FirmID NVARCHAR COLLATE Latin1_General_100_BIN2_UTF8, FirmName NVARCHAR COLLATE Latin1_General_100_BIN2_UTF8 )
Then insert data from both views into this table. This centralizes collation control and simplifies downstream queries.
Option 3: Switch to Fabric Warehouse (if feasible)
Fabric Warehouses allow you to define default collation at the database level, which is not currently supported in Lakehouse SQL endpoints. If your workload is heavily SQL-based and collation-sensitive, this might be a better long-term fit.
Summary
- Views in Lakehouse do not inherit default collation when source columns have NULL; they assign internal defaults instead.
- To avoid applying COLLATE repeatedly, define collation in views or use a staging table with unified collation.
- Consider Fabric Warehouse if you need full control over collation defaults at the database level.
- Royel
anilgavhane Thanks for your time, appling collation in each column is defficult to maintain, i just give an example with 2 source but i have lot of source and there are couple set of tables.
I am looking for, is there anything that can fix internally.
I also try it in warehouse, no luck.
- anilgavhane
From what I’ve found, the best long-term solution is to define workspace-level collation settings. Microsoft Fabric now supports setting default collation at the workspace level, which new SQL endpoints and Warehouses can inherit automatically. This helps standardize collation across artifacts and reduces the need for manual overrides.
Additionally, using Materialized Lake Views might help. These views allow you to define transformations declaratively and can be used to enforce consistent schema and collation during ingestion.
v-dineshyaCommunity Support
Hi Royel ,
Thank you for reaching out to the Microsoft Community Forum.
Why view is not capturing the default collation when the collation is NULL?
Solution: When a column has NULL collation in the source, the SQL analytics endpoint in Fabric Lakehouse assigns a default collation during view creation. This default is not necessarily the workspace default (SQL_Latin1_General_CP1_CI_AS). Instead, it often defaults to Latin1_General_100_CI_AS_KS_WS_SC_UTF8, which is case-sensitive, even though the workspace shows a case-insensitive default.
Note: The SQL endpoint is case-sensitive by default, and unless explicitly overridden, columns may inherit different collations depending on their type and origin.
How can i overcome this issue except applying COLLATE Latin1_General_100_CI_AS_KS_WS_SC_UTF8?
Solution: There is no automatic way to apply a uniform collation to all existing Lakehouse views or tables.
Please try below alternative workarounds.
1. Use Fabric Warehouses, It allow you to set collation at creation time using the REST API. You can specify Latin1_General_100_CI_AS_KS_WS_SC_UTF8 as the default collation for the entire warehouse.2. Changing the workspace collation only affects newly created Lakehouse or Warehouse. Existing resources retain their original collation settings for backward compatibility.
3. If you want to work within Lakehouse, consider creating views with explicit COLLATE clauses for key columns. You can automate this using scripts or templates to reduce manual effort.
Note: Apply consistent collation during data load or transformation. Create intermediate views with unified collation before merging. Use scripting to apply COLLATE across multiple views/tables. Prefer Warehouses for complex joins/unions.
Please refer below links.
Data Warehouse collation - Microsoft Fabric | Microsoft Learn
Solved: Lakehouse SQL Endpoint (db) Collation - Microsoft Fabric Community
Is it possible to create Lakehouse with case insensitive collation in Fabric? - Microsoft Q&A
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
