September 20, 2005 at 1:30 am
Here is a requirement:
Am creating an View with Schemabinding option., with joining 4 tables. And in one of the join outer join is used. View created successfully.
But creating a Unique clustered index on this View following error appears:
Cannot index the view VIEWNAME. It contains one or more disallowed constructs.
I know that outer join cluase should not be used in the View to create a Unique clustered index, so that the view becomes indexed view.
But for the requirement outer join is required.
Any Solution or work around is appreciated
September 20, 2005 at 5:43 am
September 20, 2005 at 11:45 pm
No It didnt workout. If you look at the example , there a null row is there at child table.
But there should not be the null row in the child table. Then this example did not help me.
Thanx-
September 21, 2005 at 7:18 am
Let's start over.
Post the table DDL, some sample data and the required output. We'll see what we can do.
September 21, 2005 at 5:04 pm
I have an approach that might help, even if it doesn't quite get you all the way to your goal. I say might help, because it depends on a couple of things.
My thought is this. Unless you are doing some kind of funky non-key join for your outer join, the 4th (outer joined) table does nothing to contribute to the uniqueness of the dataset. In that case, create an intermediate indexed view on the three inner joins, then create your ultimate view from that view outer joined to the 4th table.
I said before that it depends... The most significant thing is the execution plan of the original query. Often an outer join is the performance killer, in which case the savings from indexing the other joins could be offset by the expense of maintaining the new index.
September 21, 2005 at 11:41 pm
Here is the View Sscript
create view MyView with schemabinding
AS SELECT
ds.s_id ,
ds.e_id ,
ds.s_name ,
ds.e_name,
RTRIM(ds.ds_m) ,
ds.mat_nm ,
CONVERT(datetime,ds.adate,112) adate ,
ds.cid ,
ds.scode ,
ds.tcode ,
ds.mstamp ,
ds.ncount,
ds.ll,
bi.itypeid
FROM dbo.ds
LEFT OUTER JOIN dbo.bi ON ds.series_id = bi.series_id
WHERE e_id = 9
AND l_data = 'Y'
AND s_name is NOT NULL
AND s_name <> ''
AND s_name <> ' '
AND ds_m is NOT NULL
AND ds_m <> ''
AND ds_m <> ' '
==================
Create Unique Clustered index on MyView(s_id,e_id)
--Regards,
September 22, 2005 at 1:41 am
Is there no way that you can insert a dummy entry into your ds table that you app won't pick up?
The example in the article worked on the principle that if there isn't a match then assign it to the dummy entry.
Also looking at the strings in your WHERE clause try
WHERE LEN(ISNULL(s_name,''))>0
AND LEN(ISNULL(ds_m,''))>0
If you LEN an empty string it will return zero.
SELECTs are faster if you don't use
September 23, 2005 at 1:19 pm
Check BOL. Per the requirements for indexed views, the view can not have an outer join.
Lynn'
September 25, 2005 at 9:29 pm
Yes there is no way a dummy entry in to the table.@
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply