In SQL Server, when should I use an indexed view instead of a real table?

Question

I know in SQL Server you can create indexes on a view, then the view saves the data from the underlying table. Then you can query the view. But, why I need to use view instead of table?

Answer 1

You may want to use a view to simplify on queries. In our projects, the consensus is on using views for interfaces, and especially "report interfaces".

Imagine you've got a client table, and the manager wants a report every morning with the client's name, and their account balance (or whatever). If you code your report against the table, you're creating a strong link between your report and your table, making later changes difficult.

On the other hand if your report hits a view, you can twist the database freely; as long as the view is the same the report works, the manager is happy and you're free to experiment with the database. You want to separate client metadata from the main client table? go for it, and join the two tables in the view. You want to denormalize the cart info for the client? no problem, the view can adapt...

To be honest, it's my view as a programmer but other uses will certainly be found by db gurus :)

Answer 2

Basicaly of view:
1. When use same query (complex on many table) many times.
2. When new system need to read old table but don't want to change them.
Beside Indexed Views can improving Performance by create more specific index for a specific using but don't increase redundancy.

Answer 3

A view is simply a SELECT statement that has been given a name and stored in a database. The main advantage of a view is that once it's created, it acts like a table for any other SELECT statements that you want to write.

The select statement for the view can reference tables, other views and functions.

You can create an index on the view (indexed view) to improve performance. An indexed view is self-updating, immediately reflecting changes to the underlying tables.

If your indexed view only selects columns from one table, you could just as well place the index on that table and query that table directly, the view would only cause overhead for your database. However, if your SELECT statement covers multiple tables with joins etc. than you could gain a performance boost by placing an index on the view.

Answer 4

A table is where the data is physically stored.

A view is where tables are summarized or grouped to make groups of tables easier to use.

An indexed view allows a query to use a view, and not need to get data from the underlying table, as the view already has the data, thus increasing performance.

You could not achieve the same result with just tables, without denormalizing your database, and thus potentially creating other issues.