In SQL Server, when should I use an indexed view i

问题:

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?

回答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 :)

回答2:

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.

回答3:

One advantage of using an indexed view is for ordering results of 2 or more columns, where the columns are in different tables. ie, have a view which is the result of table1 and table2 sorted by table1.column1, table2.column2. You could then create an index on column1, column2 to optimise that query

回答4:

Basically, use a view:

1. When you use the same complex query on many tables, multiple times.
2. When new system need to read old table data, but doesn't watch to change their perceived schema.

Indexed Views can improve performance by creating more specific index without increasing redundancy.

回答5:

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.