how to use indexed view

  • I have a query that performs multiple FTS on SQl 2005 and I would like to use indexed view to optimize it. Currently I have a view that calls another view like that:

    create view My_view1 as

    select col1,col2,col3, case(when flag = 1 then 'NO', when flag = 2 then 'Y' else 'good' end) as action from my_table1

    create view My_view2 as

    select col1,col2,col3, case(when overdue= 1 then (select ID1 from My_view1 mv where col1 =t1.col1 , col2 =t1.col2 ,col3 =t1.col3) when overdue= 2 then 2 else 'good' end) as alias from my_table1 t1

    This is a very simplified logic but every time I call My_view1 from another view there is a FTS. (all good indexes and manually update statistics)

    I tried to create an indexed view to improve the performance but because of the case statement I cannot index it. Tried to create a second view that select * from My_view1 but it did not work... By removing the case statement just for test, I saw that the FTS disapeared if I use indexed view in this scenario.

    What can I do to be able to use the indexed view?

    I'm also testing if I could use a function.

    Thanks a lot, mj

  • Why not create a table 'YESNO':

    • value int,   char name
    • 1              NO
    • 2              YES

    The the first query can change the  case into a join becoming something like:

    Select a.col1,b.col2,c.col3, yn.text  from bla as a join YESNO as yn on a.flag = yn.value

    On this result you can create a indexed view.

    The 'good' will be added in the end.

  • This is what I have tried but I still cannot create unique index on the view because there are repetative values and I cannot use distict.

    So still doesn't work for me.

    Thanks a lot, mj

  • Can you post DDL, actual data of the tables and required output from the view.  That'll be the quickest way for us to provide an answer for ya.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply