How to deal with case sensitivity in Dataverse tables without using lower function in queries

[antoec]

Hi

In dataverse build by dynamics365 FNO table2 is child of table1 id field in table1 is uppercase but id field in table2 is lowercase. in query when linking both tables using id field there won't be any data due to case sentivity.

using lower function in everyquery will be expensive. Is there any configuration or setting which could be changed to deal with this permanently.

[v-csrikanth]

Hi @antoec 
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you,
Cheri Srikanth.

[v-csrikanth]

Hi @antoec 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Thanks 
Cheri Srikanth

[v-csrikanth]

Hi @antoec 
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you,
Cheri Srikanth

[v-csrikanth]

Hi @antoec 
I sincerely appreciate @nilendraFabric for taking the time to provide a thoughtful and insightful solution.
Note: For reference regarding case sensitivity, please refer to the following document.
https://learn.microsoft.com/en-us/power-platform/admin/language-collations 

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[nilendraFabric · Accepted solution]

Hello @antoec 

In Dataverse, the collation settings—including whether the data comparisons are case-sensitive or case-insensitive—are defined when the environment is created and cannot be changed afterward. This means that if your environment was configured with a case-sensitive collation, you cannot later switch it to be case-insensitive through a configuration change. Therefore, to resolve issues where related fields have mismatched cases, the only practical solutions are to normalize the data—for example, by ensuring that all key values are stored in the same case—or to use computed columns that store a pre-normalized version of the data. This approach typically avoids the performance overhead of using functions like Lower() or Upper() in every query.

there isn’t a built‐in configuration setting in Dataverse to globally disable case sensitivity for text comparisons. Instead, you must ensure that the data used for linking tables is normalized so that both sides of the join use the same case.

If this is helpful please accept the answer 

[antoec]

@nilendraFabric  thank you for the reply. Could guide any documentations or reference where i could follow and create a dataverse which is case-insensitive. 

[nilendraFabric]

https://learn.microsoft.com/en-us/power-platform/admin/language-collations

key points in the doc

Default Collation Settings:
• When creating a Dataverse environment, administrators select a default language. This choice determines the collation applied to the SQL database that underpins the Dataverse instance.
• Collation settings affect sorting, filtering, and text comparisons, including case sensitivity and accent sensitivity.

All languages in Dataverse use case-insensitive collations by default, denoted by `_CI`. For example, “Cafe” and “cafe” are treated as the same word