Editorials

NOEXPAND

Generally, I don’t like using SQL Server materialized views, sometimes called Indexed Views, because they are materialized by creating an index on the view definition. In order to create an index on the view you have to define it with phrase WITH SCHEMABINDING. What this does is map the view definition to the underlying tables in the query, even if the view is calling another view. The net result of Schema Binding is to keep the underlying tables from being modified unless the view is dropped, so that the view does not become corrupted through modifying the schema of a table used by the view.

Interestingly, there is another impact of schema binding. When you use a non-materialized view in a query or stored procedure, it builds an execution plan against the based tables of the view, basically expanding the view definition out to the individual tables, so that it can build a better query plan. With a materialized view, this is not the desired behavior. The primary reason you created a materialized view is so that the results of the view are already persisted to disk; you don’t have to compose the results of the view definition on each execution.

I was recently reminded that the default behavior in some versions of SQL Server is to expand all views referenced in a query. I had a developer ask for some help with a query that ran locally in seconds, but in a production environment had been running for four hours. We were reviewing the query plan for the query execution and came upon a reference to a materialized view, and found it was expanding the view definition, and running the query as if the index did not already exist. It was a painful query, summarizing records in a table with millions of rows.

We were able to modify his stored procedure by adding a single query hint, resulting in sub second execution in all environments, even the production database,, where the query had been running for 4 hours, and still hadn’t completed.

We added to the JOIN clause to the materialized view the following query hint.

WITH (NOEXPAND)

For example…

SELECT …

FROM myMaterialiedView WITH (NOEXPAND)

The NOEXPAND query hint tells SQL Server to use the Materialized View as if it is a table. Don’t re-constitute the results from the base tables. It should be up to date already.

So, if you are using a Materialized view, don’t forget the NOEXPAND query hint. If your instance of SQL Server doesn’t need it, the hint won’t hurt. If NOEXPAND is the desired behavior, adding the query hint assures that SQL Server will not reference the base tables, even if that is the default behavior.

This is really old news. But it is something we often forget. Also, people first using indexed views may not be aware of this behavior. If you want additional information regarding why NOEXPAND is needed in any version, take a look at this editorial.

Cheers,

Ben