Menu
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:
I've just finished a SQL Server course yesterday so don't know how to proceed. Would appreciate any comments. Cheers.
670
A legitimately needed left join was present in the view and any attempt to create a index raised a "not allowed construct" error. After hours of trial and error I managed to do it work. It's not at all an elegant approach, but it works for outer joins.
To create an indexed view, you use the following steps: First, create a view that uses the WITH SCHEMABINDING option which binds the view to the schema of the underlying tables. Second, create a unique clustered index on the view. This materializes the view.
Indexes can only be created on views which have the same owner as the referenced table or tables. This is also called an intact ownership-chain between the view and the table(s). Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema.
Q: Why can't I use OUTER JOIN in an Indexed view? A: Rows can logically disappear from an Indexed view based on OUTER JOIN when you insert data into a base table. This makes the OUTER JOIN view to be increasingly updated, which is relatively difficult to implement.
Here is an alternative. You want a materialized view of A not containing B. That isn't directly available... so instead, materialize two views. One of all A's and one of only A's with B's. Then, get only A's not having B's by taking A except B. This can be done efficiently:
Create two materialized views (mA and mAB) (edit: mA could just be the base table). mA lacks the join between A and B (thus containing all A's period [and therefore containing those records WITHOUT matches in B]). mAB joins between A and B (thus containing only A's with B's [and therefore excluding those records WITHOUT matches in B]).
To get all A's without matches in B, mask out those that match:
with ids as ( select matchId from mA with (index (pk_matchid), noexpand) except select matchId from mAB with (index (pk_matchid), noexpand) ) select * from mA a join ids b on a.matchId = b.matchId; This should yield a left anti semi join against both your clustered indexes to get the ids and a clustered index seek to get the data out of mA you are looking for.
Essentially what you are running into is the basic rule that SQL is much better at dealing with data that IS there than data that ISN'T. By materializing two sources, you gain some compelling set based options. You have to weigh the cost of these views against those gains yourself.
199
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
