5 Reasons to use Indexed Views

I love indexed views in SQL Server! My current project is a fairly large system for a government entity, containing over 70 live tables, and so the queries that I have written are intense. Many of them are so intense, in fact, that the database server's processor has decided to take its sweet old time producing results. I've been able to mitigate (but not eliminate, unfortunately) a lot of the query latency using indexed views, and I want to share some of the reasons that I chose to use them in this project. So here they are:

1. BECAUSE I CAN: For some time, I believed that you could only use indexed views in SQL Server Enterprise Edition, but this is not true. You can use indexed views in any edition of SQL Server. The only difference between editions is that the query optimizer in Enterprise Edition will automatically consider indexed views when appropriate, whereas you must explicitly specify when to use them in other editions. You can specify when to use an indexed view using the (NOEXPAND) view hint.

   
   
   

2. Narrowing a table: There are a few tables in this application that are updated infrequently and are very wide. The width of these tables, as well as the large number of records in them, caused them to slow down any query that they took part in. So I decided to create an indexed view on one of the tables, including the primary key and the few columns that are used in most of the queries referencing the table. Viola! By using the view instead of the base table, I improved query performance significantly, and the only costs were some disk space and a minimal performance hit at 3:00 AM when the tables are updated.

   
   

   Although I'm not doing so in my current application, you could also use indexed views to narrow a table horizontally. This approach requires some savvy, though, since you as a query developer need to know in advance that all of the rows you care about are in the indexed view. When a vertically-narrowed view doesn't have a column that your application needs, the query compiler will complain. When a horizontally-narrowed view doesn't have the row, end users will complain. I think you get the picture.

   
   
   

3. Using multiple clustering keys: There is one table in my current application that is used in a number of different queries. I was trying different clustered indexes on the table in an attempt to boost performance, but I was getting nowhere. When I chose one set of columns as my clustered index, then half of my queries ran quickly while the other half were dogs. When I chose another set of columns, the second set of queries started to perform while the first set slowed to a crawl. My solution: create an indexed view on the table that is the equivalent of SELECT * FROM <table>, create a clustered index on the first table containing the columns that make the first set of queries sing, and create the clustered index on the view to the columns that work for the second set of queries. By changing the second set of queries to use the view, both sets of queries now perform well.

   
   

   Now let me say that this view doubled the amount of space that my data in that table is using, since my database now has two copies of the table. Every insert, update, and delete will take twice as long, since both the real table and the indexed view need to be changed. But in this application, the performance benefit outweighed the performance cost.

   
   

4. Aggregating lots of data: Here's why indexed views were thought up, if you ask me. In this case, I have several "accounts", each of which has several credit and debit transactions. I want to be able to instantly retrieve the balance on any of my accounts, so I created an indexed view that looks something like the following:

   
   

   CREATE VIEW vwAccountTransactionBalance_Indexed
   AS
   SELECT
   AccountID,
   SUM(Amount) AS TransactionBalance,
   COUNT_BIG(*) AS TransactionCount
   FROM
   tblTransaction
   GROUP BY
   AccountID

   
   

   Simple, right? The one "gotcha" in this scenario is that an account that doesn't have any transactions won't show up in your view at all. So to make sure that all of your accounts are included, you should probably create another view like this:

   
   

   CREATE VIEW vwAccountTransactionBalance
   AS
   SELECT
   AccountID,
   ISNULL(TransactionBalance, 0) AS TransactionBalance,
   ISNULL(TransactionCount, 0) AS TransactionCount
   FROM
   tblAccount a
   LEFT JOIN
   vwAccountTransactionBalance_Indexed atbi (NOEXPAND)
   ON a.AccountID = atbi.AccountID

   
   

   Now you have a view that gives you the account balance for every account without needing to go to the transaction table at all. And there's one more benefit to using nested views like this. You can add the indexed view at any point in your development cycle. Go ahead and implement vwAccountTransactionBalance the "normal" way during initial development. Then, if performance of your queries becomes a problem, create an indexed view and reference it from within your original view. Your application performs better, and you don't need to change another line of code.

   
   
   

5. Space is cheap: There are two primary drawbacks to using indexed views, as there are with using any index: data modification performance and disk space. You ought to be mindful of the impact your indexed views are having on data modification performance, but my point here is that the disk space drawback is often a non-factor. I'm sure that the database I've been working with could be made smaller by eliminating some indexed views, but then the database server would just have that more empty disk space. If your disk is full, go buy another one! The performance benefits to your application will be well worth the price.

   
   

So that's why you should consider using indexed views. Please note though that indexed views are not the answer to every one of your query performance problems. If you create indexed views on tables that are frequently updated, you could pay a serious performance price in your data modification queries. In the application that I've been working on, though, data modification tends to be narrowly targeted: one row here, two rows there, rather then many (or even few) hundreds of rows at once. In this scenario, it's difficult to notice the delay that comes from SQL Server needing to change indexed view data as a result of my base table data changes.