SQL Server Indexed Views and View Performance

Views are a powerful table-level abstraction. You can combine and transform data from one or more tables (across databases!) into a single, cohesive, pseudo-table. Why “pseudo-table?” Views have some caveats, such as being potentially read-only. But that’s another post for another day.

Anyway, if you use views, you might be wondering how exactly they work, and how they affect performance. Are they cached? Are they more efficient than the equivalent SELECT statements?

As it turns out, the answer is “it depends.” SQL Server allows you to index views to optimize selection. (Indexing views has some caveats, and it depends on the underlying column types/aggregation.) At any rate, the implementation, according to this MSDN article, is:

  • Indexed views are cached, and
  • Non-indexed views are expanded as needed (into their constituent SELECT statement).

This means that if you use a view without an index, you can’t expect any performance gain (or loss) — it’s just a logical rearrangement of table data. On the other hand, adding any index will give you the benefit of caching. This means you can use an indexed view to replace an expensive query, because SQL Server caches the results. This is very useful if you have huge queries or jumbo, multi-table selects.

What’s more, the same MSDN page contains a veritable gem of a statement:

In SQL Server Enterprise, the SQL Server query optimizer automatically determines when an indexed view can be used for a given query execution. The view does not need to be referenced directly in the query for the optimizer to use it in the query execution plan. Therefore, existing applications can take advantage of the indexed views without any changes to the application itself; only the indexed views have to be created.

That’s a kicker of a statement. It means you can (secretly) optimize your SQL Server performance on the back-end by creating indexed views, without changing your application. This makes it easy to create/test/drop/repeat as necessary; no code changes necessary.

Simply amazing. I really suggest you read up on indexed views, and their caveats; they’re a very useful tool to have in your toolbox if you ever find a performance bottleneck in your database layer.

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Tools, Web, Wndows Forms and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *