Blog

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 my video The Okapis of SQL Server Indexes!

↑ Back to top
  1. Just a quick comment, Indexed Views can’t use COUNT() but they can use COUNT_BIG(*)

  2. 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.

  3. 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.

  4. 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/

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

  6. Great follow up post on your Pass Summit 2013 session.

    Johan

  7. 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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php