topic Re: Lakehouse SQL analytics endpoint collation is not correct in Data Engineering

Lakehouse SQL analytics endpoint collation is not correct

will-trickett — Mon, 23 Sep 2024 15:14:24 GMT

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.

Re: Lakehouse SQL analytics endpoint collation is not correct

Anonymous — Tue, 24 Sep 2024 03:07:28 GMT

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!

Re: Lakehouse SQL analytics endpoint collation is not correct

will-trickett — Tue, 24 Sep 2024 14:18:36 GMT

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.