search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.8k stars 3.2k forks source link

SQL Server: instead of dropping/creating indexes, use CREATE INDEX with DROP EXISTING=ON #35067

Open roji opened 1 week ago

roji commented 1 week ago

When an index facet is changed (e.g. the fill factor), EF currently generates a migration that drops and then recreates the index.

First, we should add support for ALTER INDEX, which would be the proper and efficient way to apply index changes; #20692 already tracks this.

However, there apparently are cases where you can't alter the existing index, but need to recreate it. For those cases, CREATE INDEX with DROP EXISTING=ON would be better perf-wise than drop/create, e.g. since it allows queries to continue using the old index as the new one is still being created (this could take a long time on big tables).