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.79k stars 3.19k forks source link

Consider adding AlterIndexOperation to Migrations #20692

Open lajones opened 4 years ago

lajones commented 4 years ago

Per discussion with @bricelam when reviewing PR #20634

Currently a FILLFACTOR change causes a DROP and CREATE of the index on which the fillfactor is defined. But in SQL Server (2008 onwards) it's possible to just do:

ALTER INDEX REBUILD WITH FILLFACTOR nn

We should consider the relative performance of ALTER INDEX versus DROP and CREATE and perhaps add an AlterIndexOperation class to migrations.

Note: several other operations are allowed on ALTER INDEX. We would need to consider which of them to support.

bricelam commented 4 years ago

On SQL Server, it looks like this syntax avoids the need to rebuild referencing foreign keys. However, this is minimized by the fact that any foreign keys created by EF will be referencing unique constraints and not unique indexes.

roji commented 4 years ago

FWIW PG also allows various index modifications. I think there's more than enough reason to do this.

roji commented 2 years ago

Another thought on this: when the migrations SQL generator sees an AlterIndexOperation with a facet that cannot be altered, it can automatically drop and recreate the index. This would save users the trouble of having to do it themselves.

roji commented 1 year ago

FWIW I just ran into this again in a PG-related thing (https://github.com/npgsql/efcore.pg/issues/2899).