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.
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.
32 Comments. Leave new
Just a quick comment, Indexed Views can’t use COUNT() but they can use COUNT_BIG(*)
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.
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.
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.
My apologies, I misspoke. I meant you can create a unique index (effectively a unique constraint) on an indexed view, even if the indexed view has some aggregation. That can act like a check constraint.
There’s a good example of this at https://spaghettidba.com/2011/08/03/enforcing-complex-constraints-with-indexed-views/
Thanks. That’s a useful example, showing how to create an indexed view to enforce ‘can never happen’ conditions; you make a view which is expected to be empty in normal operation and index it. A unique index still allows a single row to appear, at least, so you have to use the trick of cross joining a ‘two values’ table to get two copies.
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/
I feel this article does not add value over what is already on the web about indexed views.
I feel this article is exactly what was needed, a short summary of pros and cons to consider before diving deeper into each technicality!
Great follow up post on your Pass Summit 2013 session.
Johan
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.
Hahahahahahahaha. “brain dead BI systems” – tell me about it!
I would like to add that you can’t (!) truncate a table when it’s referenced by an indexed view.
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
Allen – for Q&A, head on over to https://dba.stackexchange.com
You can also replicate the indexed view…
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 *…”
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?
also can’t use derived tables, nor left outer joins, nor an inline table function
your blog is really help me alot
And you can’t create the clustered index on the view online even in Enterprise edition 🙁
How about using columnstore indexes and implement queries in stored procedures,so that we can avoid limitations of indexed views
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!
I am no expert as far as indexed views are concerned, but from what I’ve been reading, indexed views come with the downside that because the data is persisted in the DB it must also be maintained… So, if a junior DBA creates a complicated indexed view linking wide tables, or rather, the indexed view itself is wide, with fields coming from several tables, all those need to be maintained whenever the underlying tables are modified. In other words, depending on each case, you may be introducing lots of extra I/O and processing when the underlying tables are modified, because the data in the indexed view needs to be modified as well. I thought that was worth mentioning, and I believe this should have been part of this article as a caveat. Or am I missing something here? Did I make sense? I ran out of coffee for the day, so excuse me if I didn’t come out clear enough. Cheers!
Raphael – we don’t cover every limitation and gotcha in every blog post. Hope you enjoyed the post.
This isn’t right. We can use any aggregates in the indexed view as long as COUNT_BIG is included – see Itzik’s explanations of an indexed view.
@Tim, After trying it out, it seems that is not true!
I searched for, but couldn’t find an article about indexed views by Itzik (Ben-Gan I presume?). Any info would be helpful, although I’m skeptical!
Hello Brent. Thank you very much for this post, it is a really nice summary.
I have never worked with indexed views before, so I have a lot to learn (and test)
I am a bit confused on the “truncate” concept. I was expecting to be able to truncate directly an indexed view, and that statement would truncate the first table of the query (or the last right join if there were any).
Excluding the removal of the schemabinding option, which are the truncate options available?
Warmest regards
Genti – no, you don’t delete the indexed view. It’s the same as an index. You wouldn’t delete the contents of an index separately from the table.
i have created a index view with 1 fact and 3 dims . also created with one clustered and 2 non clustered indexes on that view. now i have a job which refreshed DIM tables for every 3 hrs.
while running the job the data is not refreshing the base table nor the job is failing .. its just showing as execute. can you please help me on this?
Guru – for personalized troubleshooting help, click Consulting at the top of the site.
Thanks Brent! Another “can’t” I just learned while trying to deploy a new indexed view is that you can’t have self joins on a table in an indexed view. I get the following error message back.
Cannot create index on view “Database.schema.vwMyView”. The view contains a self join on “Database.schema.MyTable”.