microsoft / microsoft/DacFx

Add support for CREATE INDEX WITH DROP_EXISTING = ON

Open
#488 4 comments 3 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

**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.

Contributor guide

Open the contributing guide

Research direction

Start by tracing how dacfx and sqlpackage generate CREATE INDEX and DROP INDEX deployment scripts, using the issue's SQL Server Enterprise examples as the expected behavior. Compare the existing DeploymentPlanModifier workaround with a profile or parameter approach. Done means deployments can emit CREATE INDEX with DROP_EXISTING = ON, optionally with ONLINE = ON, without manual script editing.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.