Lakehouse SQL analytics endpoint collation is not correct

[will-trickett]

Hello -

Our team recently started using the SQL analytics endpoint to create views for downstream analysis.  The default collation for the endpoint is SQL_Latin1_General_CP1_CI_AS, which is supposed to be case insensitive by default.

However, when we try to create views on the endpoint, it treats them as case sensitive.  For example, if the table has a column "AAA" and we create a view selecting "aaa" from the table, is throws an error that the field is not found.

Is this behavior expected?  Is there a way we can fix this?

Thanks.

[Anonymous]

Hi @will-trickett ,

In the SQL endpoint of Fabric lakehouse, the default is case-sensitive.

If you only specify a collation for a column, when you create a view and query the view, that column follows the collation you specify, while the other columns use their own default collation. So, if you only specify a collation for one column, the other columns may still be case-sensitive when querying the view.

To ensure that the entire table is case-insensitive, it's a good idea to assign the same collation to all relevant columns when you create the table, or explicitly specify a collation for each column in your query.

In addition, collates can only be used for character data types (e.g., char, varchar, nvarchar, and text), not numeric data types (e.g., int, float, decimal, etc.).

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[will-trickett]

Hi @Anonymous 

Thanks for the response. 

Are there plans to change this behavior in the future?  Or to allow us to specify a default collation at the server/database level?  It seems like this will be allowed for Fabric Warehouses based on this link, but I'm not sure if that extends to Lakehouse SQL endpoints as well?

Side note, it's not very intuitive for the server to show a collation of SQL_Latin1_General_CP1_CI_AS, and yet actually be case sensitive.  It would be helpful to get this fixed as well.