Add support for CREATE INDEX WITH DROP_EXISTING = ON

[maciejw]

Is your feature request related to a problem? Please describe.
We have a very large legacy database that runs on SQL Server Enterprise we would like to use sqlpackage out of a box, but its not possible currently because of lack of support of enterprise features such as one mentioned.
We cannot drop and create indexes like dacfx is doing currently because of performance reasons.

Describe the solution you'd like
We would like to be able to use DROP_EXISTING feature and instead of script like this

DROP INDEX SomeIndex ON SomeTable
# other scripts 
CREATE NONCLUSTERED INDEX SomeIndex
    ON SomeTable(Column1 ASC, Column2 ASC)

we would like to get a script like this

CREATE NONCLUSTERED INDEX SomeIndex
    ON SomeTable(Column1 ASC, Column2 ASC)
    WITH (DROP_EXISTING = ON, ONLINE = ON)

It would be perfect if dacfx would recognize that we target an enterprise edition, but we see also a solution with a switch in a profile or a parameter, that would force dacfx to respects a flag such DROP_EXISTING during deployment and emit scripts with it.

Describe alternatives you've considered
Currently alternatives are a bit painful, because we could either edit our deployment script by hand (we don't want to do it because of automation) or write and test and DeploymentPlanModifier, it requires a lot of effort, we could modify a script this way and during a lot of trial and error figure out all the edge cases we don't know about yet.

[jvdslikke]

WITH (DROP_EXISTING = ON) is unrelated to Enterprise edition IMO, because it's not an Enterprise feature.

I think it's always better to do an ALTER WITH (DROP_EXISTING = ON) instead of a DROP and CREATE. Because:

1. Between the drop and create there is no index, which can lead to severe performance degredation while the new index is being created.
2. When the create fails we end up with no index at all, which can lead to severe performance degredation for an even longer time.

[maciejw]

@jvdslikke DROP_EXISTING is ignored and removed by dacfx in a final script since it converts index changes as drop create, as well as ONLINE which IS the Enterprise feature that we use together.

[jvdslikke]

Hi @maciejw that's true. But in my opinion it should not even be needed to add DROP_EXISTING in the project SQL files, as this does not belong to the model. It's not about the database design, but how the publish is executed. If an index already exists, it should always be updated with DROP_EXISTING instead of drop-create.

To create indexes online you could also use the ELEVATE_ONLINE database scoped configuration setting: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver16#elevate_online---off--when_supported--fail_unsupported-