2

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.

  • Using the 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 without WITH SCHEMABINDING option. This is not SqlPackage.exe's issue. – Denis Rubashkin Jun 5 at 14:57
  • 1
    Hi @DenisRubashkin! As far as I know and have tested this isn't correct. Only those columns of a table used in the definition (of a view) are schemabound. You can modify, delete and add all other columns. You can find an example here: link – Thomas Costers Jun 5 at 15:39
  • 1
    You are right, I have tested it. My world won't be like before. – Denis Rubashkin Jun 6 at 6:39
1

SqlPackage.exe often selects a hard way to do things.
We had a following process for issues like this one:
1. Generate change script on dev environments and look for any long steps, like the one you have.
2. Manually create a script to implement needed changes (not all changes, so in your case it will be the view update). Execute this script before SQLPackage.
3. Run SqlPackage again. It should notice that this view do not require changes and will implement only other changes.

  • 1
    I was hoping there is some way to prevent manual intervention, but I'm afraid your way is the only way to prevent the recreation of the indexed view. Thanks for your input! – Thomas Costers Jun 11 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.