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?

Solomon

Having read all answers, I still cannot see any viable reason to use indexed view instead of real table – Volumeter

@Yousi, your accepted answer does not say much (or anything for that matter) about INDEXED views. – Dozen

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

Administer

Does your manager wants a report every morning with the outdated frozen at some moment of time clients balances? If he wants actualized data then it is again the same creating the same strong link between report and table but through additional intermediary (indexed view). What do you mean experimenting with database. Is database a toy? Experiment with views! I could not see any piece of any valid rationale under this logic – Volumeter

Acutally often he does. Daily Sales mayy be defined to be al sales UP TO MIDNIGHT LAST DAY - so that everyone runs the same numbers, even if running the report an hour later. Data Warehouses (typical reporting) are noramlly loaded in intervals, not real time. – Honeycutt

@TomTom, yep, you got it. In addition, you may have a very active server where complex queries of the report interface may kill actual performance. – Administer

Answer 2

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

Rugger

Since views could be joining huge tables, and applying sorting on non-indexed columns, is there value in adding indexes to the originating tables, instead of the view? I've read that an indexed view can increase contention with the underlying tables, but if your view is ordering by specific columns, why wouldn't any other? – Trichomonad

I understand the usage of views in simplifying queries, but I am not sure I understand why it is faster. Could you explain more please ? – Vitrics

Answer 3

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.

Destroyer

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.

Grower

What is the sense in "You could not achieve the same results with just tables"? Extracting some pieces of data, isolating them from database. The next step is just to drop database to ultimately increase performance. Sorry, I could not see any piece of any valid rationale under this logic – Volumeter

Answer 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.

Coruscate

If I may qoute the topic starter: " But, why I need to use view instead of table? " – Coruscate