Problem in creating indexed view
-
Hi
I am trying to create an indexed view, but I face the error message that "It contains one or more disallowed constructs".
the view returns something around 140000 rows and have to use indexed view for better performance.
although I have done all the books online written, but couldn't. It seems that there is a problem with left/right outer join, but I have to use them. I want to know the if I want to use indexed view, should I use just the inner join or it is not the real problem.
-
I don't think it's to do with the outer join. Make sure you have followed Books Online exactly. If you miss even one part you won't be able to define the index.
Two things that seem to trip a lot of people up are
- The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
- The view must not reference any other views, only base tables.
--------------------
Colt 45 - the original point and click interface
-
Does your view contain any of the following:
- TOP clause
- text, ntext, image columns
- DISTINCT
- MIN, MAX, COUNT(*), COUNT(<expression>
, STDEV, VARIANCE, AVG- SUM on nullable expression
- derived table
- ROWSET function
- Another view
- UNION
- Subqueries, OUTER joins, self-joins
- CONTAINS or FREETEXT predicates
- COMPUTE, COMPUTE BY
- ORDER BY
None of those are allowed in indexed views(according to "Inside SQL Server 2000")
My Blog:
http://dineshasanka.spaces.live.com/ -
In other words command that might be remotely useful in an indexed view.
BOL does not exclude aggregate functions but does say that COUNT_BIG should be used rather than COUNT.
It also says that if AVG, STDEV, VARIANCE are used then the query optimizer will try and derive these values from SUM and COUNT_BIG.
Can you post the source for your VIEW?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply