Add a where clause to your index


Userlevel 4
Badge +3

Goal

There are some cases, where you would like to add a where clause to an index. For example, you want to have unique article numbers in your article table, but also have the option to de-activate articles and in that special case, re-use the article number.

You could write some trigger code to make sure this is the case, but there is an easier solution by creating a unique index, combined with a where clause. The index would look something like this:

CREATE UNIQUE NONCLUSTERED INDEX ix_article_number ON article
(
article_number ASC
)
WHERE active = 1

There is only one problem, you cannot configure this in the Software Factory by default.

Solution

There is a way around this problem, making use of tags, combined with some code in the dynamic model that injects the where clause in the index creation scripts.

There are two steps involved, first you have to add a control procedure in dynamic model, containing the following code:

declare @sql varchar(2000)

--The bit of code that needs to be injected into the control, fetching the value of the index tag that contains the where clause of the index
set @sql = 'insert into prog_object_item_parmtr
select project_id = @project_id,
project_vrs_id = @project_vrs_id,
prog_object_id = ''index_'' + x.indx_id,
prog_object_item_id = ''index_'' + x.indx_id,
parmtr_id = ''where_clause'',
parmtr_value = it.value,
order_no = 1,
no_line_when_empty = 1
from indx x
left join indx_tag it
on it.project_id = x.project_id
and it.project_vrs_id = x.project_vrs_id
and it.indx_id = x.indx_id
and it.tag_id = ''index_where_clause''
where x.project_id = @project_id
and x.project_vrs_id = @project_vrs_id'

--update the control procedure code, so it adds an extra parameter / value to contain the where clause
update cp
set control_proc_code = control_proc_code + CHAR(10) + CHAR(13) + @sql
from control_proc cp
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and control_proc_id = 'sql_indexes'

--expand the template, adding an extra parameter called where clause
update cpt
set template_code = replace(template_code, '[STORAGE_ID]', '[STORAGE_ID]' + char(10) +char(13) + '[where_clause]')
from control_proc_template cpt
where project_id = @project_id
and project_vrs_id = @project_vrs_id
and control_proc_id = 'sql_indexes'
and template_id = 'index'

After you have added the dynamic model control procedure, you can add where clauses to any index in your project using an index tag, for example this one: 

 

 


0 replies

Be the first to reply!

Reply