Materialized View and Left Join

Question

From what I gather: left, right, and outer joins are not allowed in materialized views, nor are unions or group-bys.

What I want is to get 3 columns from 1 table, and an additional Min Value from a separate 1:many table.

For example:

Item (table):

Id, Name, Date, List<Price>

Price (table: will join on Price.ItemId = Item.Id):

Id, ItemId, Value

An Item has many Prices, Price has one Item.

I want 3 columns from Item (Id, Name, Date), and the min value in Price (Value)

Not every Item will have a Price yet, hence the need for a left join.

From the numerous posts I read about workarounds, the general suggestion is to denormalize the data into a new table for the materialized view to read from.

Now, my question is, the new denormalized table has all the data I want, so what good is the view?

Please tell me this isn't the preferred MS approach... I have an extra table that I didn't want in the first place, that has all the same data as the view, all to support the creation of a new materialized view.

Answer 1

you can use a table maintained by triggers instead of a materialized view. the restrictions on materialized views are for performance.

Answer 2

The advantage with the indexed view is that the optimizer may decide to use the view, even if you don't list the view explicitly in the query. This cuts both ways, so if even if you mention the view in the query explicitly, the optimizer may scrap not use the view. More exactly, the algebrizer expands the view to its definition. The one exception is if you specify the NOEXPAND hint.

If you have your own table, this will never be an issue.

No matter you have your own table or an indexed view, there is quite a bit of overhead for maintaining the view/table. But you will to write more code on your own to maintain the table.

Answer 3

Hi @David Wright

As far as I know, Materialized View is not supported in SQL Server.

I have an extra table that I didn't want in the first place, that has all the same data as the view, all to support the creation of a new materialized view.

You could use the original script to update the data in the table or use a MERGE statement.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".