Hello SQL Experts,
Have one table at warehousing side which contains 100 millions of rows. Actually I succeeded to reduce performance(by adding missing indexes) with great extent in case when worst criteria is used but still it is not at acceptable level.
There can be 25 fields used as criteria to query on this table. Some other tables are also using in join but cost of joining those table is low.
Then I come across Indexed view where it is possible to store aggregate result before hand and I can query to this indexed view with NOEXPAND option but designing index view is critical task for me.
1. Do I have to add all fields/columns into view definition which can be part of criteria?? and grouping all those columns to store aggregate results??
2. Also I have to create unique clustered index on this view. which fields I have to to consider part of it?
3.Other non clustered index I do add as required but will this hamper lot of maintenance cost?
If you already have designed indexed views please do provide your views for the same.
hope so I have explained problem here very well.
Many thanks in advance.