Managing hash table with multiple hash columns in a database project for Synapse

Question

Hi, in order to implement a database for a Synapse dedicated SQL pool I'm working inside a Visual Studio 2022 environment with SSDT. I need to create some hash distributed table having some hash columns and not only one. To build these hash tables I need to apply this alter statement:

ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;

The scripts related to these hash distributed tables in the database project go in error also the script having the above alter statement. Any suggests to me to solve this issue? Thanks

Answer 1

I have a doubt about the compatibility level of your DB. Check if 50 is appropriate for your Synapse SQL pool because it may affect certain behaviors in the DB and should be set with consideration of the features and functions you're using. Otherwise, verify that the script is being executed in the correct context. The ALTER DATABASE SCOPED CONFIGURATION command needs to be executed in the context of the DB you intend to alter. If possible share the error so we can help you :)