What’s in Enterprise only? (View Substitution Implicitly Allowed)

As with any index, SQL Server can decide to use an indexed view (materialized views) in its query plan only if the query optimizer determines it is beneficial to do so.

Indexed views can be created in any edition of SQL Server. But only in the Enterprise Edition, the query optimizer automatically considers the indexed view.

In all other editions you must explicitly specify the NOEXPAND table hint as part of the query, in order for the optimizer to consider view substitution and view matching during the query optimization process.

This post is part of the a series I decided to call “Understanding the value of the Enterprise Edition, one feature at a time” which I started in September 2011 and will grow on a weekly basis, having a new post incorporated to the family every Friday.