• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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
0
apresto
Asked:
apresto
  • 2
1 Solution
 
HilaireCommented:
Please post view definition
sp_helptext yourview
0
 
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
AS
SELECT
      PAFMatchType,
      CustomerNumber,
      CustomerID,
      DomicileID,
      PersonID,
      CDID,
      Title,
      Initial,
      Surname,
      CASE
        WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFPostCode
         ELSE CustomerPostcode END AS Postcode,
      CASE
        WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFAdd1
        ELSE CustomerAdd1 END AS Add1,
      CASE
          WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFAdd2
        ELSE CustomerAdd2 END AS Add2,
      CouncilTaxBand,
      CarMake,
      CarModel,
      ModelDerivative,
      VehicleLicenceNumber,
      RegistrationDate,
      DateOfSale,
      SaleMansName,
      CustomerPostCode,
      GoneAwayInitialAndSurname,
      GoneAwaySurnameOnly,
      MortaScreenInitialAndSurname,
      MortaScreenSurnameOnly,
      BereavementInitialAndSurname,
      BereavementSurnameOnly,
      MPSSurnameOnly,
      Duplicates,
      Businesses,
      Telephone1,
      Telephone2,
      Telephone3,
      Telephone4,
      TPS1,
      TPS2,
      TPS3,
      TPS4,
      MatchType,
      NewOrUsed
FROM
      dbo.tblCustomerDataMatchTypes
--------------------------
0
 
rafranciscoCommented:
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.
0
 
aprestoAuthor Commented:
i thought that was the case, oh well, thanks rafrancisco

Apresto
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now