Filtered indexes

Related products: Software Factory

Since a recent version of the Software Factory it is possible to create unique indexes that exclude NULL values. This is a useful feature, however, I think it would be great if we can take it one step further.

I’d like to propose the possibility to create filtered indexes, where the developer him/herself can supply a where clause that will be applied to the index.

For myself, I have this working using some dynamic model fiddling, but I think it would be pretty straight-forward to implement as a default feature.

This is an example from the index script that is being created using my adjustment:


/* Create index 'ix_un_def_dimensions_len' on table 'article_default_dimension'. */

*snip*

create unique nonclustered index ix_un_def_dimensions_len
on "article_default_dimension"
(
article_id,
default_length
)

where default_width is null
go


/* Create index 'ix_un_def_dimensions_len_width' on table 'article_default_dimension'. */

*snip*

create unique nonclustered index ix_un_def_dimensions_len_width
on "article_default_dimension"
(
article_id,
default_length,
default_width
)

where default_width is not null
go


These indexes ensure that I can have unique records for either article_id / length where width has not been provided and have unique records for article_id / length / width where width has been provided.

The only thing the developer has to do, in my example, is provide the where clause, i.e.

where default_width is null

At the moment, I use a tag on the indexes to do this, but this could just as well be a field on the index-table/screen.

 

 

Updated idea status OpenOn the backlog

When will this be available? I can see many uses for this.


Updated idea statusOn the backlogPlanned

Updated idea statusPlannedWorking on it!

Updated idea statusWorking on it!Next release (2022.2)
 
 


 

Updated idea statusNext releaseCompleted