We have many automated deploys of in-memory and regular databases. Build produces dacpac, then SqlPackage.exe performs deploy. Recently we've faced an issue when tried to add new column to the table in IN-MEMORY database. There was a function dependent on this table, of course with both NATIVE_COMPILATION and SCHEMABINDING options set.
SqlPackage solved SCHEMABINDING lock on a table as it would do for a regular database and non-natively compiled function - it generated deploy script with these steps:
- alter the function to remove SCHEMABINDING option (but keep NATIVE_COMPILATION option)
- alter the table
- alter the function back with SCHEMABINDING.
And this deployment fails. Because NATIVE_COMPILATION function requires SCHEMABINDING option and generated deployment script is a priori invalid in this part.
I've registered a bug fix request. Asking here if anyone has a workaround or knows how to fix this. Currently we "fix" such issues with manual pre-deployment script which drops all blocking dependencies, then the deploy restores such functions. And it does not smell good.
Example of generated deployment script part (obfuscated):
GO
PRINT N'Removing schema binding from [my].[fn]...';
GO
ALTER FUNCTION my.fn
(@bar INT NULL)
RETURNS TABLE
WITH NATIVE_COMPILATION -- this is an illegal set of options
AS
RETURN
SELECT t.id
FROM dbo.altered_table AS t
WHERE t.foo = @bar
GO
PRINT N'Altering Table dbo.altered_table...';
GO
ALTER TABLE dbo.altered_table
ADD new_col INT NULL;
GO
PRINT N'Adding schema binding to my.fn...';
GO
ALTER FUNCTION my.fn
(@bar INT NULL)
RETURNS TABLE
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
RETURN
SELECT t.id
FROM dbo.altered_table AS t
WHERE t.foo = @bar
GO
SqlPackage.exe version: 16.0.5400.1
SqlServer version: Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64)
Target database compatibility level: 150
Standalone server (not azure)
upd
SqlPackage 162.0.52 also has this bug
