CODEWITHSUNDEEP
sqlsql-serversql-server-2014
IShubh
IShubh

Reputation: 364

difference between view and indexed view or materialized view

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.

Upvotes: 8

Views: 22324

Answers (2)

Xedni
Xedni

Reputation: 4695

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.

Upvotes: 4

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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.

Upvotes: 8

Related Questions