Schema bound view is not blocking table rebuilds

[robck]

• SqlPackage or DacFx Version: 162.5.57.1
• .NET Framework (Windows-only) or .NET Core: 8.0.11
• Environment (local platform and source/target platforms): sqlpackage cli to azure sql database

Steps to Reproduce:

1. Create a sqlproj project
2. Add a table and a view WITH SCHEMABINDING to the table
3. Publish sqlproj to a sql server database
4. Alter the table in a way requiring the table to be rebuilt (e.g. new column in the middle of table)
5. Publish sqlproj to the sql server database
6. Publish succeeds by dropping the views schema binding, rebuilding table, then adding schema binding back to the view

I expected that the table would not get rebuilt. If the schema binding simply gets dropped, there is no value in schema binding.

Did this occur in prior versions? If not - which version(s) did it work in?
yes

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[dzsquared]

Can you provide more details on this example, like the table and view scripts? Does the view have explicit column references to the table, or is it SELECT *? I'm trying to understand how removing/re-adding view schema binding to allow your table alteration was the undesirable deployment.
It would absolutely be a bug if the view became non-operational from the deployment.

In general, we consider the project to be the declared model for how you want the database to look after the deployment, and determine the deployment plan to modify the database to match (without data loss). In your project, the schema binding on the view is to the version of the table you're deploying to.

A deployment option to preserve schema binding statements is an option we could explore in the future, similarly to how we have the property /p: AllowTableRecreation that can be set to false to block table rebuilds.

[robck]

Yea correct - the view has explicit column references to the table.

I want to prevent table rebuilds when there is a schema bound view to it. The schema binding has no value/is useless if publishing removes and re adds it. That is the purpose of schema binding.

My only option is to set /p: AllowTableRecreation for all tables in the database. However, most tables do not have schema bound views and are ok to be rebuilt.

Another configuration option to preserve schema binding would do the trick, but I expected the schema binding to be the true indicator, and a separate configuration option isn't needed. A configuration option might be a good way forward to avoid a breaking change