Indexed Views

Good afternoon everyone!

So, now you know how to create indexes on columns of your choice within your tables using both the User Interface and also in SQL Code as well. This purpose of today's mini blog is to show you how to add Indexes to your Views.

Why Would I Add An Index To A View? 

Adding Indexes to a column greatly increases the performance of reads against that particular table. Seeing as Views are SELECT statements joining other tables to form one returning result set, it means that the performance of your views can be greatly increased using Indexes as well.

Because you will rarely update a View (as it will have an impact on any Tables related to it and you will most likely receive an error message), it means that you will gain from the benefits of Indexes but will not suffer the negatives (slower writes).

However...

Note that once indexes are applied to a view, it will now actually store data rather than just provide the lookup to it. If data is modified within any of the tables that the view is based on, the view will automatically update.

Things You Need To Consider...

When indexing a view, the first index has to be a UNIQUE Clustered Index. However once this has been created, you can add as many Non-Clustered Indexes as you need.

However before you begin adding the UNIQUE Clustered Index, you will need to:

• Ensure that the SET options are correct for all tables that will be referenced in the view; existing and tables you are creating.
• Ensure that WITH SCHEMABINDING is on. 
• Ensure that the view definition is deterministic.

For a full list of all SET options for Indexed Views, take a look at the link below:

http://msdn.microsoft.com/en-us/library/ms191432.aspx

There's quite a large list of requirements needing to be met before you can create your Index on your View, however once these have been met the actual creation of the Index is exactly the same as creating Indexes against a table, only you reference the View instead of the table (as shown below): 

What Next...

Tomorrow, I will be discussing Computed Columns, another method of potentially speeding up your queries. Stay tuned!

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.