Currently SqlPackage.exe used in conjunction with BitBucket for the deployment of the database schema changes.
Recently a pretty large indexed view has been created based on 3 source tables, which are key to the application. The process of creating the clustered index on the view and the accompanying downtime took around an hour to complete.
Lately a column had to be added to one of the source tables of the indexed view. Although the view has schemabinding, this shouldn't be a problem because the new column isn't used in the definition of the view (duh!). Although if we look at the script that has been generated by SqlPackage, it chooses to drop the indexed view to cope with the schemabinding although this is completely unnecessary. It seems to me that SqlPackage is unable to check the dependencies and therefore drops the view out of precaution.
If I simulate a similar situation on my local machine (SQL Server 2017 Dev edition), comparing DacPacs always result in the dropping and recreating of the view as well.
Now the question: does anyone know any workaround or any way to prevent SqlPackage dropping the view? I've checked all properties, but there doesn't seem to be any viable option there.
WITH SCHEMABINDING
option prevents any modifications to the referenced objects. No matter is a column used in definition or not. So before changing a table you must recreate objects in which the table is included so that they will be created withoutWITH SCHEMABINDING
option. This is not SqlPackage.exe's issue.