SQL: Do Indexed Views really require Enterprise Edition of SQL Server?

According to Wikipedia, Betteridge’s law of headlines is an adage that states: “Any headline that ends in a question mark can be answered by the word no.” In this case, they’re correct because the answer is no. But as with most things in this industry, there are subtle issues with that answer.

In SQL Server, a view is just a SELECT statement that’s been given a name. That’s pretty much it. No data is stored, and every time you access the view, the query optimizer expands the view into the surrounding query. By the time you get to an execution plan, the view is nowhere to be found.

Let’s see an example:

The WideWorldImporters database has a view called Website.Customers. I’ve selected from it and then asked for an estimated execution plan. If you look all through the plan below and even search the XML for the plan, you’ll find that the name Website.Customers isn’t part of the plan. That’s because SQL Server takes the T-SQL code from the view and pushes it back (i.e. inlines it) into the surrounding query, which is then optimized.

So what are indexed views?

An indexed view is a curious beast. It’s created by adding a clustered index to the view. That makes it actually store the data. There are lots of rules and limitations surrounding indexed views, and we’ll talk about them another day, but the important concept for now is that they hold the data, it’s updated automatically as the underlying data changes, and the view itself can supply the data in a query.

In the AdventureWorks database, there is a view called Person.vStateProvinceCountryRegion. Note that it has an index:

Indexed Views and Query Plans

Note the following query plan:

You can see that the query retrieved the data from the view. It did not expand the view. If we’d like it expanded, we can request that:

So what about Enterprise Edition?

The query optimizer considers indexed views in Enterprise Edition, Evaluation Edition, and Developer Edition. In other editions, the views are expanded before optimization. So there is a perception that Standard Edition and Express Edition cannot use indexed views.

However, if you always specify a NOEXPAND hint in your queries, you can use indexed views just the same in both Standard Edition and Express Edition.