Indexing a View

Hi all i am trying to index a view, i have switched ANSI_nulls and Quoted_Identifier ON, Schema Binding is on, the view is not using another view as its source, it is using a table but i am getting this error

"The optimizer cannot use the index because the select list of the view contains a non-aggregate expression"

any idea's?

is it because i am using a CASE statement - can i index a view with a case statement
LVL 23
Who is Participating?
According to Books Online (in CREATE INDEX):

Nonaggregate SELECT lists cannot have expressions.

In your case, this will be the CASE statement, which is not allowed when creating indexed views.
Please post view definition
sp_helptext yourview
aprestoAuthor Commented:
Thanks for the reply

im not sure what you mean by definition but here is the view:

CREATE   VIEW qryCustomerDataMatchType_Refined WITH SCHEMABINDING
        WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFPostCode
         ELSE CustomerPostcode END AS Postcode,
        WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFAdd1
        ELSE CustomerAdd1 END AS Add1,
          WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFAdd2
        ELSE CustomerAdd2 END AS Add2,
aprestoAuthor Commented:
i thought that was the case, oh well, thanks rafrancisco

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.