4

I have created a view which is using LEFT OUTER JOIN in SQL Server.

I am unable to index the views because you cannot put an Index on a view with an OUTER JOIN.

Any alternatives?

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
Ankit
  • 133
  • 4
  • 16

1 Answers1

3

Here is an article describing the use of ISNULL(table_id, 0) to create an INNER JOIN with the affect of an outer join. It may be of use to you.

The example in this article is good, should be fairly self-explanatory.

indexedViewsWithOuterJoins

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
  • well i tried it already but still indexing is not possible as outer joins are not allowed in indexed views.... – Ankit Nov 21 '11 at 07:35
  • Sorry @Ankit, missed that point in my reading. I've updated my answer for you with a work around that has been used. – Adam Wenger Nov 21 '11 at 07:44
  • That's a pretty ugly workaround - it requires you to populate your tables with "null" rows. – Damien_The_Unbeliever Nov 21 '11 at 08:13
  • @ Adam well had a look seems to be heavy task..m pulling out data from 10-11 tables and compiling it one view using left outer join... – Ankit Nov 21 '11 at 09:29
  • Thanks @danihp That was my initial answer too. I have edited the question to try to help the next person who reads it :) – Adam Wenger Nov 21 '11 at 18:21
  • @Adam well i guess need to restructure my tables as it seems a problem achieving inner join performance advantage using outer join.. – Ankit Nov 22 '11 at 10:27