CODEWITHSUNDEEP
visual-studioazure-synapsesql-server-data-toolssqlproj
Fabio Carello
Fabio Carello

Reputation: 1102

SSDT SQL Project for Synapse Set Scoped Configuration DW_COMPATIBILTY_LEVEL

I have a Visual Studio 2022 SSDT SQL Project used for the design and deployment of a Synapse SQL Dedicated Pool. The project is targeting the platform MS Azure SQL Data Warehouse.

The database should have some tables with a multi-columns hashing distribution. This means that the distribution for certain tables is defined upon multiple columns, like this:

CREATE TABLE [dbo].[Sample]
(
    col1 int NOT NULL,
    col2 int NOT NULL,
    col3 VARCHAR(10) NULL
)
WITH
(
    DISTRIBUTION = HASH(col1, col2),
    CLUSTERED COLUMNSTORE INDEX
)
GO

Normally this would generate an error:

SQL71657: Cannot create a multi-column distributed table.

Recently, Synapse introduced support for this kind of hashing by modifying the database compatibility: enter image description here

Note that this is a SCOPED CONFIGURATION of the database itself named DW_COMPATIBILITY_LEVEL and should not be confused with the classic compatibility level option of SQL Server.

The issue is that I cannot find any option that helps me to set up this configuration into the SQL Project in order to make aware the compiler about the new compatibility.

I tried also with a pre-deployment script that performs:

ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;

But the error keeps to pop-up at build.

I think this is just an incompatibility between SSDT extension and Synapse features and found a workaround but it is quite expensive in terms of computational overhead. Do you have any similar experience? Do you think is there any way to, at least, bypass the error during the Dacpac creation?

Upvotes: 1

Views: 315

Answers (1)

John Kanakis
John Kanakis

Reputation: 1

To resolve this you should add the line:

<PropertyGroup> ...  <DbScopedConfigDWCompatibilityLevel>50</DbScopedConfigDWCompatibilityLevel>

in the .sqlproj file

Upvotes: 0

Related Questions