Enable snapshot (materialized/indexed views) for Mssql

The docs don't mention that this isn't supported for MSSQL so we wasted a good amount of time trying to get it working until we stumbled upon the topics on the community.

Having Indexed Views could speed up important parts of applications so I hope this was simply forgotten and will be added as a high priority item on backlog ;)

Hi ​@AndreKemmeren,

Although I am not the person to make a judgment on the priority of this idea, I can create a ticket for our Documentation team to clarify the current state. Thank you for bringing this to our attention, and apologies for the time this cost you.

SQL Server does not support exactly the same snapshot capability as DB2 MQTs and Oracle MVs.

Yes, an MQT or MV may function the same as an SQL Server indexed view. This means retrieving data will be faster as it is cached and the data will always be kept up-to-date, at the cost of extra calculations during data modifications in tables used by the backing query. A resulting drawback of indexed views is that they are schemabound. This effectively ‘locks’ the underlying tables and functions so they cannot be dropped or modified without first dropping the indexed view.

In all situations, these auto-refreshing snapshots require the backing select query to be deterministic. You may not use current user functions, current date functions, randomizing functions and such.

The big difference between SQL Server indexed views and DB2 MQTs/ Oracle MVs is that the latter two can also be configured to be filled on-demand. So instead of refreshing on every table modification, logic or the user may initiate a refresh of the data. At this point, non-deterministic select queries may also be used.

This is the default way that snapshots (MQT’s, MV’s) work in the Thinkwise Platform. A refresh of the snapshot is done using logic, scheduled or indirectly by the user itself.

Implementing this capability cannot be done with an indexed view. There is no such thing as a deferred refresh. Offering such capabilities in SQL Server would require a completely different approach - this would be more in the direction of a regular table with a provisioned refresh procedure.

The question is, which behavior should we focus on for snapshots in SQL Server? Solely the read-performance aspect (indexed views) or the snapshotting capability (table + procedure)?

The schemabound aspect of it is exactly the reason we theoretically could implement this ourselves but with huge drawbacks in every scenario we thought of.

If I understand your response correctly the other db engines give the control of when the data is updated to the “user”. Implementing such functionality in Thinkwise for MSSQL would give us more control, we have implemented functionality like this on a few places.

For us the indexed views would be enough control even considering the limitations but with the snapshot capability as long as we can also have the system do it on every change it would work.

When the implementation is based on snapshot tables and an auxiliary update procedure, we cannot automatically have the system do it on every change as the backing select query may be non-deterministic and the platform is not aware of changes in the direct and indirect underlying tables affecting the backing select query.

When the implementation is based on indexed views, we will run into the schemabinding limitations, and we will not be able to defer updating. The schemabinding limitations will have impact on the order and the impact analysis for smart upgrades as well as prevent arbitrary source code execution from the Functionality screen.

We cannot have automatic updates without schemabinding issues and we cannot have snapshots which automatically update.