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 nullAt 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.
