Skip to main content
Completed

Filtered indexes

Related products:Software Factory
  • November 7, 2019
  • 6 replies
  • 253 views

Robert Jan de Nie
Thinkwise blogger

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.

 

 

Did this topic help you find an answer to your question?

Jeroen van den Belt
Administrator
Forum|alt.badge.img+9
Updated idea status OpenOn the backlog

Michael
Sidekick
Forum|alt.badge.img+2
  • Sidekick
  • January 5, 2022

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


Jeroen van den Belt
Administrator
Forum|alt.badge.img+9
Updated idea statusOn the backlogPlanned

Mark Jongeling
Administrator
Forum|alt.badge.img+23
Updated idea statusPlannedWorking on it!

Jeroen van den Belt
Administrator
Forum|alt.badge.img+9
Updated idea statusWorking on it!Next release (2022.2)
 
 


 

Jeroen van den Belt
Administrator
Forum|alt.badge.img+9
Updated idea statusNext releaseCompleted


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings