Indexed Views Don't appear to be equivalent to Oracle Materialized

Question

Hi All,

It has been quite a while since I used SQL Server and had been primarily working on Oracle DBs. I had grown accustomed to building materialized views off of my OLTP tables to speed up performance. I was excited to find information around what appeared to be the SQL Server equivalent of a Mat view...however, when I started reading and testing I realized I couldn't do things like sub-queries..reference other views, etc when building a view I was going to index. Are there any other techniques within SQL Server that folks use to speed up query times and build "reporting layers" off of the existing OLTP tables?S I could add a lot more indexes, etc to the base tables, just really wanted to see if I could accomplish something simliar to Mat views.

I could totally be incorrect on the indexed views...still very ignorant, so if I am missing something let me know.

Thanks!

-S

Answer 1

Yes indexed views have a lot (and I mean a LOT) more limitations than Oracle's materialized views. What you can do to speed up reporting is create pre-calculated tables and have processes that update those. You can also use partitioning to manage ever growing tables

Answer 2

For heavy use OLTP apps, I will usually created separate reporting tables or db during off-hours, due to the additional load updating the indexed views puts on the system. For not frequently-updated data though, indexed views can be fantastic.