Collation conflict in Microsoft database project for Azure Synapse Analytics Serverless SQL Pool

Question

I am working on a Lakehouse project in Synapse Azure. I have created a Serverless SQL Pool with the collation Latin1_General_100_BIN2_UTF8. This collation is recommended by Microsoft for parquet und Delta tables, see MS Doc Best practices for serverless SQL pool in Azure Synapse Analytics.

The collation of the master database is the SQL_Latin1_General_CP1_CI_AS as default.

I have created a database project in Visual Studio 2022 (Version 17.8.5) for deployment purposes. I did the required settings, like setting the target platform to "Azure Synapse Analytics Serverless SQL Pool" and the collation "Latin1_General_100_BIN2_UTF8" under the "Database Settings".

When I run Schema compare, I receive the following error:

Unexpected exception caught during population of source model: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_BIN2_UTF8" in the UNION operation.

Then, I tried to compare the Schema with Azure Data Studio, I got the same exception.

I do not want to use the default collation of the master database. I am not sure if this is a bug, as the Database Project considering the collation of the master database instead of the target database, or if I am missing some other configurations.

Answer 1

You can check if the collation Latin1_General_100_BIN2_UTF8 is applied to the database you are working with by using the syntax below to check the current collation:

SELECT DATABASEPROPERTYEX('db02', 'Collation') AS Collation;

The DatabasePropertyEx function, when given 'Collation' as the property parameter, retrieves the current collation settings for the specified database.

To modify the default collation for a serverless SQL pool database, you can utilize the ALTER DATABASE statement. For example:

ALTER DATABASE db02 COLLATE Latin1_General_100_BIN2_UTF8

Results:

Reference: SO thread How to change Collation in a Synapse Serverless Database

Database collation support for Synapse SQL in Azure Synapse Analytics