Does FluentMigrator support creating a filtered index?

Question

Is it possible to create a filtered index with FluentMigrator? The scenario is that I want to create a unique index on a column that may contain NULLs, so the filter should exclude rows with NULL for the indexed column.

I've modified an index in SQL Server 2012 generated by FluentMigrator to use such a filter and can confirm that it works well, so the remaining piece of the puzzle is to generate this option.

Corina

Answer 1

As this is a very Sql Server specific feature you might as well fall back to sql.

One of the reasons for the fluent style is that it is not database specific so the same migration can be run for different database types. But if you are only ever going to use Sql Server and want to use database specific features then the great thing about FluentMigrator is that it allows you to execute sql statements. This is recommended for advanced stuff that we will never support in FluentMigrator and for changes in Stored Procedures.

It would simply be:

Execute.Sql(@"CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;");

Postgres has partial indexes too so this could be something that we will add to FluentMigrator in the future.

Silvern

It's also possible to run script files included as either embedded resources or files on the disk. Check the Execute.EmbeddedScript and Execute.Script methods. – Communication