Best practice for indexed views and aggregates tables

Hi, all experts here,

Thank you very much for your kind attention.

I am having some questions on indexed views and aggregate tables.

My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?

I am looking forward to hearing from you.

Thank you very much in advance for your help.

With best regards,

Yours sincerely,

Well...it depends. Indexed views are great because they keep the data from a view materialized...but the database engine must maintain the indexes, which may slow OLTP operations. An aggregate table may be a good idea, but also might be complicated to design and make sure it is updated properly, not to mention that you have to make sure it is updated.
Tim|||

Hi, Tim,

Thank you very much for your kind advices.

In my case, the aggregates residing in a historical data warehouse, where the update of data is not so much likely and it will not have much to do with any operational database. As what you advised, indexed views would be a better idea in this case?

Looking forward to hearing from you.

With best regards,

Yours sincerely,

|||If the data isn't getting updated often, then I personally would design an aggregates table. That will give you a lot of flexibility for your project...flexibility that may be prohibited by the restrictions on designing indexed views (there are a lot of things you need to adhere to so that your view can be indexed). Hope this helps.|||

Hi, Tim,

Thanks a lot for your advices. They've been very helpful.

With best regards,

Yours sincerely,