Case insesitive data warehouse

[vasalari]

I have a database on-premise which is case insensitive  and I want to upload this into Fabric data warehouse.   All my SSIS package and scripts are written base on ignoring case.  How can I do that?  Can I create insensitive data warehouse in a workspace in Fabric

appreciate the help

[v-tsaipranay · Accepted solution]

Hi @vasalari ,

You are correct in noting a current limitation in Microsoft Fabric: cross-database queries using three-part names (e.g., DbName.dbo.Table) are not supported in either Fabric SQL Database or Fabric Data Warehouse.

This limitation is due to the distributed architecture of these services, and is detailed in the official documentation here. While Fabric SQL Database supports case-insensitive collation (configurable via the REST API, as shown here), it still doesn’t allow querying across databases, which is crucial for your scenario involving frequent joins across multiple databases. On the other hand, Fabric Data Warehouse is case-sensitive by default and doesn't support cross-database queries.

Currently, there's no Fabric SQL engine that supports both features simultaneously. As a workaround, you might consider consolidating key tables into a single database or using a Lakehouse with shortcuts and views to aggregate data. We also suggest submitting your use case to the Fabric Ideas portal to inform future feature development.

I hope this gives you an idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay · Accepted solution]

Hi @vasalari ,

You are correct in noting a current limitation in Microsoft Fabric: cross-database queries using three-part names (e.g., DbName.dbo.Table) are not supported in either Fabric SQL Database or Fabric Data Warehouse.

This limitation is due to the distributed architecture of these services, and is detailed in the official documentation here. While Fabric SQL Database supports case-insensitive collation (configurable via the REST API, as shown here), it still doesn’t allow querying across databases, which is crucial for your scenario involving frequent joins across multiple databases. On the other hand, Fabric Data Warehouse is case-sensitive by default and doesn't support cross-database queries.

Currently, there's no Fabric SQL engine that supports both features simultaneously. As a workaround, you might consider consolidating key tables into a single database or using a Lakehouse with shortcuts and views to aggregate data. We also suggest submitting your use case to the Fabric Ideas portal to inform future feature development.

I hope this gives you an idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay · Accepted solution]

Hi @vasalari ,

Could you please confirm if you've submitted this as an idea in the Ideas Forum? If so, sharing the link here would be helpful for other community members who may have similar feedback.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum  we’ll be happy to assist.

Thank you, and please continue to use the Fabric Community Forum.

[vasalari]

Hi v-tsaipranay

So mannnnnnny thanks for your comments

The issue is not resolved 

I Created a database TestDb and a table TestTbl and a field MRN  int.  Inserted few lines.  I can not run                     "select * from TestDb.dbo.TestTbl" 

Msg 40515, Level 15, State 1, Line 16, Reference to database and/or server name in 'TestDb.dbo.TestTbl' is not supported in this version of SQL Server

[vasalari]

I Created a database TestDb and a table TestTbl and a field MRN  int.  Inserted few lines.  I can not run                     "select * from TestDb.dbo.TestTbl" 

Msg 40515, Level 15, State 1, Line 16, Reference to database and/or server name in 'TestDb.dbo.TestTbl' is not supported in this version of SQL Server.

[vasalari]

Data Warehouse in Fabric is not generally case sensitive. Created SQL database which is case sensitive but has the issue of cross database table access.    I can not  run SELECT * from DbName.TablName.FieldName.   It says my version is not supporting that.  Is there a version which supports that?  The problem is SQL DB is distributed and whethet this can  create an issue.   Access data from other database on same server is a major part of my work

Thanks

[vasalari]

I thank you for your response.   The solution was good and made me happy until I tried cross database query.  It failed.  SQL in FABRIC does not support cross-database queries.  

Msg 40515, Level 15, State 1, Line 16, Reference to database and/or server name in 'DbName.dbo.PATIENT' is not supported in this version of SQL Server.

50% of my work is bewtween about 5 databases

[v-tsaipranay]

Hi @vasalari ,

Thank you for reaching out to Microsoft fabric community forum.

Microsoft Fabric Data Warehouse does not currently support case-insensitive collation. By default, it is case-sensitive, which means your existing SSIS packages and scripts relying on case-insensitivity may need adjustments (e.g., using UPPER()/LOWER() functions).

However, Microsoft Fabric SQL Database supports configurable collations, including case-insensitive ones, and may better suit your migration if preserving case-insensitivity is critical.

For more details, please refer to the SQL database Overview (Preview) - Microsoft Fabric | Microsoft Learn.

Currently, the only method available for creating a case-insensitive data warehouse is via REST API. This article provides a step-by-step guide on how to create a warehouse with case-insensitive collation through the REST API.

Please refer the following documentation :How To: Create Warehouses with Case-Insensitive (CI) Collation - Microsoft Fabric | Microsoft Learn

I hope this gives you an idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay]

Hi @vasalari ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.