Why would you create a materialized view vs. creating a new table? Is there a difference?
As I understand it incremental views get updated when the underlying data changes, but is there anything "extra" that you get from materialized views as compared to a table?
CodePudding user response：
Well, as you already noticed in your question, the fact that a Materialized View (MV) is updated regularly respectively when the underlying data changes already is something extra/different that you get compared to creating another table hat holds the same data. Since you are not copying data (which would then be reduntant), you don't need to take care of keeping multiple copies in sync / up to date.
You can also use MV's for fast retrieval of joins or aggregate data, that are pre-computed and stored and, again, kept in sync with the underlying table data.
In SQL Server, the concept closest to Materialized Views is are Indexed Views. There is a number of constraints that need to be satisfied if you want to create an Indexed View, see MS documentation. Indexed Views are updated automatically, no maintenance needed.
CodePudding user response：
Pros of Materialized View over Normal Table
- Performant
- Updated automatically
- Database Normalization
- Less to No maintenance overhead
The cons part is: Materialized View cannot be implemented with the base table which are very frequently updated., Technically we can but it's the worst practice because it gives storage/size issue.