Views vs. Indexed Views

Let’s consider the benefits and drawbacks of SQL views.

Querying multiple tables can be easier with views. It’s shorter to write a query against a single view (SELECT * FROM MyView) compared to a query specifying multiple tables joined to return a result.

Views can also be used to enhance security by only allowing users to view specific columns in the underlying table(s).

This flexibility comes at a cost and that cost grows with the size of your data. When a view is queried, SQL Server still has to pull the data from the tables referenced in the view. It’s what’s inside that counts.

Are you stuck with a view as a query bottleneck? Consider creating a unique clustered index (and then any additional nonclustered indexes) on the view to improve performance.

Let’s Get Physical

Creating indexed views can improve query performance because the data is physically stored. Instead of checking the view and doing all of the processing work gathering data from multiple tables, the physical indexed view is queried just as a table would be queried.

But I Heard Indexed Views are Enterprise Edition Only?

Kind of. With Enterprise Edition, the query optimizer will use an indexed view if it is the optimal way to go. In other editions, you’ll want to use the NOEXPAND hint to force the indexed view to be used instead of the regular view.

See the consolidated screenshot taken from the SQL Server 2019 editions and supported features page for some edition to edition indexed view differences:

Creating an Indexed View

If you’ve created an index on a table then you know how to create an indexed view.

Keep in mind that the view must have the WITH SCHEMABINDING option. The view joining data can be in place and you’ll see data read from the underlying tables:

You can then create an index and see your plan go from table reads to reads from the indexed view:

CREATE UNIQUE CLUSTERED INDEX Indexed_View_Example
   ON dbo.ComicPub (ReleaseDate, Comic_Title);
GO

Thanks for reading!