Recently, I was working on an optimization project for one of the large organizations. While working on one of the queries, we came across a very interesting observation. We found that there was a query on the base table and when the query was run, it used the index, which did not exist in the base table. On careful examination, we found that the query was using the index that was on another view. This was very interesting as I have personally never experienced a scenario like this. In simple words, “Query on the base table can use the index created on the indexed view of the same base table.”
If you have skipped the first paragraph, I suggest you go over the first paragraph one more time and try to understand the intended meaning. According to me, this is a very interesting observation and we rarely come across such an instance. I have made very small attempt to recreate this particular behavior.
USE tempdb
GO
-- Create Table
CREATE TABLE IndexViewAggr (ID INT)
GO
-- Insert One Hundred Thousand Records
INSERT INTO IndexViewAggr (ID)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Create View on the table
CREATE VIEW ViewAggr WITH SCHEMABINDING
AS
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID
GO
-- Following query is very identical to View
-- Run the query on base table
-- Please note that in execution plan there is Table Scan
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID
GO
-- Create Index on the View
-- Please note that this index is on View and not on table
CREATE UNIQUE CLUSTERED INDEX IndexView ON ViewAggr(ID)
GO
-- Run the query on base table again
-- Please note that in execution plan there is Index Scan
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID
GO
-- Please note that there are difference in "Query Cost"
-- Clean up Database
DROP VIEW ViewAggr
GO
DROP TABLE IndexViewAggr
GO
Now let us quickly observe two performance comparison of two of the selects. One object is created before the creation of the index over the view, and the other object is created after the creation of the index over the view. Please note that we are talking about two different objects here. Let me explain this schematically.
This is a very interesting behavior as the index created on view affects the query that runs on the base table. Let me show you the execution plan of the query before and after index creation.
From this example, it is very clear that there is a very big difference between the query costs when the index is created on the table. In fact, there is a very simple explanation of this behavior in the white paper Improving Performance with SQL Server 2008 Indexed Views.
The reason for this behavior is that the query optimizer works when it checks for the index on view and finds that the execution plan of the query is similar to that of the view.
Let me know your thoughts about this article. I do understand that this concept is slightly complicated. Further, this type of behavior is rarely encountered in real world. I hope that this article will give you a better insight to such a situation.
Reference : Pinal Dave (https://blog.sqlauthority.com)
27 Comments. Leave new
MS Dynamics NAV uses this feature on many large tables.
Index views are used a lot in this ERP system.
Yeah good find ! I learnt this concept only 5 days ago.
Indexed views adds complexity …
Also they must stay in sync with the underlying tables as data changes.
* Should be used only when speed of data retrieval outweighs overhead of data modification
* these are good for highly static data
* Are bad for high volume transactional data
Thanks for another great post! Too bad the automatic consideration of an index on a view only happens with the Enterprise version.
Thanx for this one.
Very interesting!
Yes, this is a great feature. But, this automatic substitution is done only in enterprise and developer edition. On rest of the editions we need to explicitly query the view to use the index view.
This is very helpful article,Thank you mr.Pinal Dave
I have learned SQL server’s Indexes before but not clearly,after reading your article i understood well
irrespective of how i run the before and after query, it shows the same results. (i am not running the query for the second time to think that it might be from cache).
also, after the index on view has been created, the query we run still shows Missing index….
executed on sql 2008 sp1.
On editions lower than Enterprise, I believe you can force use of the view’s index with the NOEXPAND hint. But it won’t happen automatically.
Tried to run the same query, but I’m still getting the same cost. Could you tell me why it is so?
Working on SQL 2005 SP1
Regards
Dharamvir Singh
very nice analysis.
I think The view is a imaginary table. It was referring the original table records so the index also created automatically
It occurs to me that the schemabinding requirement for indexed views might allow the sql engine to consider bound objects in developing the execution plan. Just a thought.
@Chris Z: that’s correct about the schemabinding, although schemabinding is really required just to allow the view to be indexed in the first place.
@BartD: the NOEXPAND hint forces SQL Server to work with the view as a view, rather than expand it to its base definition during query normalization. The most common use of this parameter is for when the optimizer wants to service a query against an indexed view by hitting the base data instead of using the indexed view data. In this case, it’s the other way around – we want a query against a base table to use the indexed view.
In general, remember that there isn’t any huge difference physically between indexes and (clustered) tables. If the optimizer understands the data connection between one or more indexes / tables, it will use them as it deems best. In Enterprise or Developer Edition, this can be done with indexed views, which are really just another automated form of indexing data from one or more tables.
I am getting the same result, even after creating a index on view, and execute a query used in view.
I have a problem with this feature : if the view return LESS records than the table, the query return a bad result…
when i am using above script my estimated plan showing same value each time 100% why?
can you explain in detail
Very nice Analysis :)
It would be better if we get more details on SQL Server Query execution plan..
This is really helpful article, Thanx
interesting as far as the inner workings of SQL Server, but nobody seems to be asking the obvious question – would you ever purposely design it this way? Isn’t it much cleaner and logical to create an index on the base table? One scenario I can think of is when you have a database created by a vendor that you do not want to contaminate with your own code, yet you need to improve its performance. Even then the indexed view needs to be in the same database for this to work, which is still not ideal.
Hi Mr. Pianldave,
I always read your post. This is a good post.
I am navice in sql server.
Would you please tell me is Index view stored in the database.