Monthly Archives: March 2011

Indexed (materialized) views in Microsoft SQL Server

10 Replies

Even if it sounds almost the same as the regular views, indexed views are completely different animals. That type of the views are not only about the abstraction but more about performance. When you create the indexed view, SQL Server “materializes” the data in the view into physical table so instead of doing complex joins, aggregates, etc, it can queries the data from that “materialized” table. Obviously it’s faster and more efficient.

Let’s take a look at that using our favorite Clients and Orders table. Before we begin, I’d like to mention that there are quite a few requirements you have to met when you create the indexed views. And quite a few limitations. You can get more information in MSDN (http://msdn.microsoft.com/en-us/library/ms191432.aspx).

So let’s run the query that return the list of the clients who spends more than 900,00 for the orders together with # of orders.

Now let’s create the indexed view.

Now let’s run the query against this view.

As you can see the situation is dramatically improved. But that’s not all. Now let’s run the original statement in Enterprise edition of SQL Server and see the plan. And this is the magic – even if you don’t reference the view in the select, SQL Server founds that it can use the view for this select.

This is in fact very good optimization technique if you need to deal with 3rd party applications. If vendor does not allow you to change the indexes on the tables, you can create indexed views and SQL Server Enterprise edition will use them automatically. Unfortunately this is not the case with other editions but Enterprise and Developer. Let’s see that:

With the standard edition of SQL Server it does not even use “materialized” data by default. If you want to force SQL to use the view data, you have to use (noexpand) hint.

Obviously, other magic, like using the view indirectly would not work either.

What does it mean for you? First of all, if you expect to support different editions of SQL Server backend, you should keep this behavior and noexpand hint in mind. Obviously optimization technique for 3rd party applications would not work either.

Last thing I’d like to show is performance implications. Let’s insert the new order.

As you can see, it introduces nice performance hit because of the view support. Similar to the indexes – you have to pay the price of view maintenance for the benefit of performance improvements. Is it worth to do in your system? Hard to say especially if you have heavy loaded OLTP system. For Data Warehouse/Reporting/OLAP systems it could greatly benefit you. Another thing to keep in mind – indexed views shine when you use them with the aggregates.

Source code is available for download

Views in Microsoft SQL Server (Part 2 – Schemabinding and things to avoid)

Leave a reply

Last week we saw a few potential problems that views can introduce because of the extra IO and joins. Today I’ll show you the biggest “bad practice” with views and the simple way how to avoid it. All of us are lazy and sometimes it really backfires..

Let’s use the same tables (Clients and Orders) we had last week. Let’s add another column to Orders table and create the simplest view.

Now let’s query the view.

Simple, don’t you think? Now let’s add another column to the view and repeat the query.

As you can see, it does not appear in the result set. Now let’s do the opposite action and drop both columns.

As you can see nothing prevents us from doing that. Although, if we run the same select again, we will get the following error.

If we check the catalog views, we can see that Col1 is still there. Unfortunately this is only the part of the problem. You can “recompile” view with sp_refreshview stored procedure but still…

Now let’s do another test. Let’s create another view first and next add and drop columns.

Now, if we run the select again, it would work although results would be completely screwed up. It returns Placeholder data as Amount (dropped column) and new Col1 data under Placeholder column. Can you imagine all wonderful side effects you can have on the client side?

So speaking about that, first rule is never ever use “select *” in the view definition. This is the source of all potential problems. Second, the good practice is to use schemabinding option. With this option SQL Server links the view and underlying schema and does not allow any changes that can break the views.

Source code is available for download