Re: Indexed View

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"Michael C" <mikec@xxxxxxxxxx> wrote in message news:uN%239p5C1JHA.1420@xxxxxxxxxxxxxxxxxxxxxxx
I'm playing around with turning an existing view into an indexed view. This view has been used throughout our app so hopefully speeding up this view will improve speed in many places. I managed to get it all working except for one part. The view looks something like this

SELECT Blah1, Blah2, etc
FROM dbo.Table1
JOIN dbo.Table2 ON Table1.Blat1 = Table2.Blat2
WHERE this = that etc

So far so good but we have a table which simply lists rows that should be excluded, which we add to the query with a not exists

AND NOT EXISTS (SELECT * FROM dbo.ExcludeTable WHERE ExcludeTable.Blah = Table1.Blah)

The alternative is to use a left join but indexed views don't like that either. Is there anything I can do? I know I can change the data structure (to, say have an exclude bit in table1) but I didn't make these tables and changing them would require a lot of pain. Is there anything I can do without changing the tables?

Thanks again,
Michael


Hi Michael

You can't have sub-queries, EXCEPT , UNION or INTERSECT in an indexed view so it will not be possible to do what you want in a single view. The error message you should have got, does suggest the alternative of creating a separate indexed view for each part, i.e make the exclusion outside the view.

John

.

Relevant Pages

  • Indexed View
    ... I'm playing around with turning an existing view into an indexed view. ... view has been used throughout our app so hopefully speeding up this view ... say have an exclude bit in table1) ...
    (microsoft.public.sqlserver.programming)
  • [kde] exclude kde apps from fontAA
    ... When calling remote X-apps over a ssh -X tunnel I'd like to exclude that app ... from fontAA for performance reasons - hoq do I do that for kde/qt-apps? ...
    (KDE)
  • Re: Exclude apps from Software Update
    ... You can exclude specific updates from being applied, ... like the Airport update or Disk firmware, but it's not based on a ... Next time there's an update for this app it won't show up on Software ...
    (comp.sys.mac.system)
  • Re: ClickOnce XmlSerializers.dll.deploy
    ... the fix for me was to go to the publish tab in the project ... XmlSerializers.dll to "exclude" under Publish Status. ... My app still ... had not issues with clickonce but now it looks like the build is missing ...
    (microsoft.public.dotnet.languages.csharp)