Well to be sure, you can't index a view having a self join. Well really even two joins of the identical table, even when it isn't technically a self join. A few men from microsoft emerged having a deal with. But it is so complicated I do not comprehend it!!!
The reply to the issue is here: http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html
The vista I wish to apply the work around to is:
create VIEW vw_lookup_test WITH SCHEMABINDING AS select count_big(*) as [count_all], awc_txt, city_nm, str_nm, stru_no, o.circt_cstdn_nm [owner], t.circt_cstdn_nm [tech], dvc.circt_nm, data_orgtn_yr from ((dbo.dvc join dbo.circt on dvc.circt_nm = circt.circt_nm) join dbo.circt_cstdn o on circt.circt_cstdn_user_id = o.circt_cstdn_user_id) join dbo.circt_cstdn t on dvc.circt_cstdn_user_id = t.circt_cstdn_user_id group by awc_txt, city_nm, str_nm, stru_no, o.circt_cstdn_nm, t.circt_cstdn_nm, dvc.circt_nm, data_orgtn_yr go
Any help could be greatly apreciated!!!
Thanks a lot ahead of time!
EDIT : And So I discovered that this can work. Observe that I join towards the table once within the first indexed view, and also the second amount of time in teh second non-indexed view.
alter VIEW vw_lookup_owner_test2 WITH SCHEMABINDING AS select count_big(*) as [countAll], awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, circt_cstdn_nm, data_orgtn_yr, dvc.circt_cstdn_user_id from dbo.dvc join dbo.circt on dvc.circt_nm = circt.circt_nm join dbo.circt_cstdn o on circt.circt_cstdn_user_id = o.circt_cstdn_user_id group by awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, circt_cstdn_nm, data_orgtn_yr, dvc.circt_cstdn_user_id go
CREATE UNIQUE CLUSTERED INDEX [idx_vw_lookup_owner2_test1] ON [dbo].[vw_lookup_owner_test2] ( [awc_txt] ASC, [city_nm] ASC, [str_nm] ASC, [stru_no] ASC, [circt_nm] ASC, [circt_cstdn_nm] ASC, [data_orgtn_yr] ASC, [circt_cstdn_user_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
create view vw_lookup_dvc_loc as select awc_txt, city_nm, str_nm, stru_no, circt_nm, o.circt_cstdn_nm as [owner], --o.circt_cstdn_user_id, t.circt_cstdn_nm as tech, data_orgtn_yr from vw_lookup_owner_test2 o With (NOEXPAND) join circt_cstdn t on o.circt_cstdn_user_id = t.circt_cstdn_user_id group by awc_txt, city_nm, str_nm, stru_no, circt_nm, o.circt_cstdn_nm, data_orgtn_yr, t.circt_cstdn_nm --o.circt_cstdn_user_id
I'm able to then create additon indexes around the first view as If only. I am unsure if the solution (or even the workaround for your matter) will really accelerate preformance but i'll inform you.