difference between view and indexed view or materialized view

Question

I am confused in these two and tried to figure out the differences but didn't get something specific for which I am looking for.

• Where to use indexed view over an ordinary view.
• Some important differences between them.

Celeriac

@MJH If you don't have the answer then skip it and as I mentioned in my question that I tried to figure out so that means I did some research on it. And I know that very well for what purpose this site is. – Celeriac

@Evaldas Buinauskas have answered my question very well. Thank You – Celeriac

Answer 1

The key difference is that materialized view is well, materialized. This basically means that data is being persisted into a virtual table which is maintained by SQL Server itself.

This has both benefits and issues. Notable benefits:

• Commonly used queries can be encapsulated in a view and indexed in order to improve read performance (compare running select from a single table versus, for instance, 5 tables that are joined)
• Aggregations can be precomputed and would also improve read performance

Drawbacks:

• It will certainly impact write performance because with each DML operation, SQL Server will have to update view. This can be observed in execution plans
• It can negatively impact replication performance if subscriber creates a material view from a replicated table
• A lot of restrictions in order to create an indexed view
• If you're using a non-enterprise SQL Server version, WITH (NOEXPAND) hint must be added, otherwise SQL Server will expand view and will just run SQL statement within it and totally ignore index.
• DBAs usually tend to avoid them because they add extra maintenance.

Ryun

Please don't use Oracle specific terminology to refer to a SQL Server specific similar function. SQL Server has indexed views, not materialized views. – Eastwood

When you're indexing view, need to make schema binding. This means, that all used in view schema cannot be modified till indexed view exists. And, if youre performing schema edit via t-sql, some related objects may be dropped silently. Example: you have indexed view, and full text search on it. – Brewage

@Eastwood a materialized view is not an Oracle specific term; it's literally what an indexed view is. – Honeysuckle

@Damien Microsoft documentation calls it material view. learn.microsoft.com/en-us/azure/architecture/patterns/…, also you're more than welcome to edit my answer to correct terminology. – Ryun

@EvaldasBuinauskas That URL is about the cloud pattern of a materialized view, the actual feature in SQL server is called an "Indexed View". learn.microsoft.com/en-us/sql/relational-databases/views/… – Aerobe

Answer 2

Views (unindexed) is really nothing more than a way to put a query in a nice, clean, table-like thing. It takes up no space, because it doesn't contain anything until it's queried. There are almost no restrictions on what you can or cant put in said query.

Indexed views are just what they say on the tin. They're views, but indexed. What that means is that it materializes the view and keeps it up to date via the transaction log and stuff.

Why not index every view? Basically they come with a laundry list of limitations, the potential for maintenance and blocking issues, and you lose many of the lightweight nature of a normal view.

End of the day, if you need an indexed view, you need an indexed view. But by default, they're probably more trouble than they're worth.

Honeysuckle