Indexing a View

Posted on 2005-05-11
Last Modified: 2008-02-01
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
Question by:apresto
    LVL 26

    Expert Comment

    Please post view definition
    sp_helptext yourview
    LVL 23

    Author Comment

    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,
    LVL 28

    Accepted Solution

    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.
    LVL 23

    Author Comment

    i thought that was the case, oh well, thanks rafrancisco


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now