views:

300

answers:

1

I know Oracle offers several refreshmode options for their materialized views (on demand, on commit, periodically). Does Microsoft SQLServer offer the same functions for their indexed views?

If not, how can I else use indexed views on SQLServer if my purpose is to export data on a daily+ on-demand basis, and want to avoid performance overhead problems? Does a workaround exist?

A: 

A materialized view in SQL Server is always up to date, with the overhead on the INSERT/UPDATE/DELETE that affects the view.

I'm not completely sure of what your require, you question isn't completely clear to me. However, if you only want the overhead one time, on a daily+ on-demand basis , I suggest that you drop the index when you don't need it and recreate it when you do. The index will be built when you create it, and it will be then up to date. When the index is dropped there will not be any overhead on your INSERT/UPDATE/DELETE commands.

KM
Thanks for the answer. What I really want is the same feature in SQL Server that Oracle has. That is the on-demand only refresh option, in order to avoid the overhead. But as far as I have found out now SQL server cannot support that.Your suggestion seems feasible. The big question is of course the construction time of a new indexed view each time I create it.Do you know if SQL Server has different Build methods to choose from? (I know Oracle has 1) populated when created or 2)populated by the next refresh operation) I guess the DROP statement is rather quickly to run?
MOLAP