Need some serious help with self join issue.

Posted by kralco626 on Stack Overflow See other posts from Stack Overflow or by kralco626
Published on 2010-06-15T14:22:26Z Indexed on 2010/06/15 23:42 UTC
Read the original article Hit count: 321

Filed under:
|
|
|
|

Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!!

The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12/creating-indexed-view-with-self-join.html

The view I want to apply this 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 would be greatly apreciated!!!

Thanks so much in advance!

© Stack Overflow or respective owner

Related posts about sql

Related posts about database