What You Can (and Can’t) Do With Indexed Views

Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation.

  • Security: we create views so that a user can read specific columns out of certain tables, but not all the data.
  • Simplification: sometimes, it’s easier to write a SELECT against one view than a SELECT against multiple tables, with joins – repeatedly.
  • Aggregation: again, it can be easier to SELECT already-aggregated data than to write the query – repeatedly.
This is my kind of view

This is my kind of view

The down side to views is that when you query them, you’re still reading data from all of the underlying tables. This can result in large amounts of I/O, depending on the number and size of tables. However, you can create a unique clustered index on the view – referred to as an indexed view – to persist the data on disk. This index can then be used for reads, reducing the amount of I/O.

There are some limitations to indexed views, but when you can create one and it improves performance, it really improves performance. But, as with all features in SQL Server, indexed views aren’t the answer to everything. Here’s a quick look at some things you can and can’t do with them.

You Can…

The view definition can reference one or more tables in the same database.

Once the unique clustered index is created, additional nonclustered indexes can be created against the view.

You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.

You Can’t…

The view definition can’t reference other views, or tables in other databases.

It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.

You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.

You can’t always predict what the query optimizer will do. If you’re using Enterprise Edition, it will automatically consider the unique clustered index as an option for a query – but if it finds a “better” index, that will be used. You could force the optimizer to use the index through the WITH NOEXPAND hint – but be cautious when using any hint.

Choose the Right Tool

If you’re looking to have a complicated, aggregated query persisted to disk to reduce I/O, an indexed view may be the right tool for your job. Test it as an option, and if it works, put it to use!

Learn more about indexed views in Mastering Index Tuning.

Previous Post
Update on Stack Overflow’s Recovery Strategy with SQL Server 2014
Next Post
Free Ebook: SQL Server DBA Training Plan

22 Comments. Leave new

  • Just a quick comment, Indexed Views can’t use COUNT() but they can use COUNT_BIG(*)

    Reply
  • Something that you allude to but don’t specifically highlight is that in Enterprise, the engine will consider the index on the view even if you don’t directly reference the view, which I think is a great feature.

    A SELECT that pulls an aggregate present in an indexed view can get that aggregate from the view’s index without touching the table, which is really powerful.

    Reply
  • There’s one other handy use for indexed views: a kind of constraint.

    Let’s say I have an Addresses table:

    CREATE TABLE Addresses
    (AddressID int not null primary key clustered
    ,CustomerID int not null — FK to a Customers table
    ,AddressLocation varchar(max)
    ,IsDefaultAddress bit not null)

    I want to have 0 or 1 default addresses per customer. How do I enforce that? You can make an indexed view:

    SELECT
    CustomerID,
    ,count_big(*) as DefaultAddressCount
    FROM Addresses
    WHERE IsDefaultAddress = 1

    Then you can create a CHECK constraint on DefaultAddressCount to make sure it’s never more than 1.

    Reply
    • Can you give an example of creating a check constraint on an indexed view? I’ve tried to with MSSQL 2008 R2 but it always results in a syntax error.

      Reply
  • Yet another use for indexed views is to emulate a filtered index. Since filtered indexes weren’t introduced until SQL Server 2008, this can come in handy when you encounter older versions (I just finished a client project on SQL Server 2000, so you never know).

    Here’s my T-SQL Tuesday post from 3 years ago where I describe how to do this – http://noelmckinney.com/2010/10/filtered-index-alternative/

    Reply
  • I feel this article does not add value over what is already on the web about indexed views.

    Reply
    • I feel this article is exactly what was needed, a short summary of pros and cons to consider before diving deeper into each technicality!

      Reply
  • Great follow up post on your Pass Summit 2013 session.

    Johan

    Reply
  • Charles Kincaid
    November 25, 2013 10:51 am

    One of the other uses for views is denormaization. You sort of dealt with this under “simplification”. I get plagued by – no – have the honor of working with folks who are plagued by having to deal with brain dead BI systems. I have needed to write many views to denormalize or structure so that the BI system can import data.

    By the way. My web spell check wants to change denormalize to demoralize. On a cold Monday morning the humor was welcome.

    Reply
  • Gert Nelissen
    August 14, 2014 2:19 am

    I would like to add that you can’t (!) truncate a table when it’s referenced by an indexed view.

    Reply
  • Hi Brent, Indexed view works well in 2012 but when in sql2014 or later the update take a long time. Any tweaking needs to be done. Please advise

    Reply
  • You can also replicate the indexed view…

    Reply
  • You CAN modify the underlying tables, including ADD, DROP, ALTER column. You just cannot modify any column that is referenced by the view. So, ensure the view explicitly references only the columns that it needs to, and not “select *…”

    Reply
  • We are using a package (Argos) that uses strings in NVARCHAR (unicode) format, but our tables only use VARCHAR resulting in conversions and index scans. If I do not want to use a computed column (i.e. CONVERT VARCHAR to NVARCHAR), can we do this with an Indexed View, i.e. the view would hold a list of the field values converted to NVARCHAR?

    Reply
  • Gustav Retief
    May 22, 2018 4:49 am

    also can’t use derived tables, nor left outer joins, nor an inline table function

    Reply
  • your blog is really help me alot

    Reply
  • And you can’t create the clustered index on the view online even in Enterprise edition 🙁

    Reply
  • How about using columnstore indexes and implement queries in stored procedures,so that we can avoid limitations of indexed views

    Reply
    • Murthy – well, this post is about indexed views, but if there are other features you’re interested in, definitely check out our posts on those. Thanks!

      Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}