Using indexed views? Don’t forget this important tip.

Today after fighting with designing a view that let me create a clustered index on it (indexed views — also known as materialized views — are awesome in the right context!), I kept running into the same problem whenever I clicked Display Estimated Execution Plan in SQL Server Management Studio.

The problem was that the plan was doing a table scan on the underlying table, and not using the indexes I had carefully crafted.

I had even created a second non-clustered index to try and make sure it was a proper covering index for the query.

It was then that I remembered I was working on Standard Edition of SQL Server 2016, and when we use indexed views on non-Enterprise editions, we need to include the WITH (NOEXPAND) query hint to let the query optimizer know that we have indexes:

SELECT col1, col2 FROM view1 WITH (NOEXPAND);

Once I added the hint, the query plan looked as expected. For seasoned Transact-SQL aficionados, this was an obvious oversight on my part, but truth be told, I’ve been spoiled lately with Enterprise Edition.

File this one under “things to do in all editions of SQL Server, so that it works the same way on all editions of SQL Server.”

Photo by Maksym Kaharlytskyi on Unsplash.