Add support for CREATE INDEX WITH DROP_EXISTING = ON
- 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
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