How to mimick Oracle Materialized Views on MS SQL Server?

Question

Application connected to MS SQL Server will create views where a single row result is an analysis including aggregations of 1-10k records. The applicable criteria across the resulting view will have dozens to tens of thousands of results. The view+criteria will then be ordered by some column (user specified) in the view which are most likely to be the aggregated columns. Response times are expected to degrade quickly when aggregated column is used for ordering.

A while back, this problem was solved pretty easily (in Oracle 9i) with materialized views. Any ideas on how to get a similar solution in MS SQL Server 2005.

Answer 1

You can use Indexed views for this.

Read here for SQL 2005: http://msdn.microsoft.com/en-us/library/dd171921.aspx

Read here for SQL 2008: http://msdn.microsoft.com/en-us/library/dd171921.aspx