Indexed View to Improve Multiple Connection Performance on SQL Server

Question

I have a query that performs a join on many tables, which results in poor performance.

To improve performance, I created an indexed view, and I see a significant improvement in the performance of a view request with a date filter. However, my concern is with index storage. From what I read, a unique clustered index is stored on SQL Server. Does this mean that it stores separately all the data that is part of the joins in the view? If so, if I included all the columns from the tables that are part of the joins in the view, will the disk space on the consumed server be approximately half the disk space without an indexed view? And every time I enter data into the base tables, are the data duplicated for the indexed view?

Answer 1

Yes this is correct. An indexed view stores all the data in the view separately from the source tables. Depending on the columns and joins, the data is duplicated and can be many times larger than the original tables.

Answer 2

It is right. The indexed view is basically an additional table that contains a copy of all the data in a sorted way. This makes it so fast, but, like everything in SQL Server, it is expensive - in this case, additional storage and additional time are required to synchronize all copies of the data.

The same is true for a normal index in a table. This is also a copy of the index keys (plus some information on where to find the source line), which requires additional storage and additional time during supported updates.

To find out if adding an index to a table or view makes sense, you need to look at the whole system and see if a performance improvement for one query can degrade the performance of other queries.

In your case, you should also (first) check if additional indexes in the base tables can help your queries.

Answer 3

Quite a lot, yes. You made a compromise in which you get better performance in exchange for additional efforts with the help of the engine, as well as additional storage necessary for its preservation.