Noble_Bright_Life Noble_Bright_Life - 1 month ago 13
SQL Question

What to replace left join in a view so i can have an indexed view?

I have normalized tables in a database and to denormalize it, I created a view out of two tables. When I tried to create a clustered index on the view, it wouldn't let me, as the view was created with a left outer join. I used a left join because I want the null values to show up in the resulting view, much like how it was suggested in this earlier post.

Question on join where one column one side is null

The table structure and relationship is very much similar to what was described in the above link.

I seemed to hit a wall here as I couldn't convert my left join into an inner join, as that would exclude all records with null values on any of the joined columns. My questions are:


  1. Why is indexing not allowed on outer or self joins?

  2. Are there any performance hits on this kind of un-indexed view?

  3. Anyone knows any workaround to this problem?



I've just finished a SQL Server course yesterday so don't know how to proceed. Would appreciate any comments. Cheers.

Answer

There is a "workaround" here that involves check for NULL in the join and having a NULL representation value in the table

NULL value

INSERT INTO Father (Father_id, Father_name) values(-255,'No father')

The join

JOIN [dbo].[son] s on isnull(s.father_id, -255) = f.father_id