Update: Please read the summary post of all the 11 Limitation of the view SQL SERVER – The Limitations of the Views – Eleven and more… Let us learn about Index Created on View not Used Often.
I have heard many people saying that if they create a view and index on it, this will reduce the load on the original table as all the subsequent queries on view will not access the basic table. This is not true always and the view may not give you the performance optimizations which you are looking for.
In the following example, we will create the base table first. We will then create the view on top of it and create the index on the view. Subsequently, we will run the simple SELECT statement on base table and on the view. It is noticed that the view does not pick up the index created in the view, but uses the base index created on the original table. It will also expand the underlying table and use the index created on the base table. If you assume that after creating the index on the view, the view is materialized and has no relation with original table, then this can very well be incorrect.
USE tempdb GO IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]')) DROP VIEW [dbo].[SampleView] GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U')) DROP TABLE [dbo].[mySampleTable] GO -- Create SampleTable CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100)) INSERT INTO mySampleTable (ID1,ID2,SomeData) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name), ROW_NUMBER() OVER (ORDER BY o2.name), o2.name FROM sys.all_objects o1 CROSS JOIN sys.all_objects o2 GO -- Create Index on Table CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable ( ID1 ASC ) GO -- Select from table SELECT ID1,ID2,SomeData FROM mySampleTable GO -- Create View CREATE VIEW SampleView WITH SCHEMABINDING AS SELECT ID1,ID2,SomeData FROM dbo.mySampleTable GO -- Create Index on View CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView] ( ID2 ASC ) GO -- Select from view SELECT ID1,ID2,SomeData FROM SampleView GO
Now let us observe the execution plan and the use of index along with the query.
Querying Original Table
Querying View
You might have noticed that the View used the same index as original table and did not use the index created on the table. The reason is as follows: the optimizer has decided that the original index will be more beneficial in query plan than the newly created index. I tried clearing the cache, but the same result is obtained every time.
If you really want your view to act as an independent table and have no relation with the original underlying table, then you can use the hint [noexpand] and the view will not expand. We will see this in some other article in future.
Let me know if you want to read about any other limitation of the view.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Hi Pinal,
You are right. View might get expanded so that the actual data is read from the table instead of the view. Like you said NOEXPAND prevents expanding so if you run following queries you’ll see the difference:
SELECT ID1,ID2,SomeData
FROM mySampleTable
SELECT ID1,ID2,SomeData
FROM SampleView
WITH (NOEXPAND)
The latter one should have lower query cost compared to the former one.
But the example is, forgive me, pretty bad one. Indexed view does not do any good in this situation since the view is actually as big as the original table. The real benefits lies in joins and aggregations of large datasets. I urge everyone to read more from here:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc917715(v=technet.10)
There’s lots of stuff which I didn’t know even existed, like all those SET options which must be set before creating and index to the view.
Hi Pinal,
I am working with SQL Server since 2 years, I know the difference between ‘RIGHT OUTER JOIN’ and ‘LEFT OUTER JOIN’, I know when to use ‘RIGHT OUTER JOIN’ or ‘LEFT OUTER JOIN’. but the thing is that which one is better…
‘SELECT * FROM A LEFT JOIN B ON (A.ID = B.ID)’ or
‘SELECT * FROM B LEFT JOIN A ON (A.ID = B.ID)’ …..
sorry
queries like
‘SELECT * FROM A LEFT JOIN B ON (A.ID = B.ID)’ or
‘SELECT * FROM B RIGHT JOIN A ON (A.ID = B.ID)’
Hi Pinal,
Our team faced an issue in reporting services. We use stored procedures in ssrs. our stored procedure mostly executes max with in 1- 3mins( depends on customer),But in application it takes more than 30 mins. so my manager wants me to optimize the stored procedure it might decrease in application to get the report.
Please help me out with some kind of suggestions.
Thanks in advance
Run the procedure from SSMS and see how long it takes. If it returns in a minute or two and the application is taking 30 minutes to finish then the problem must be in the application code and not in the procedure.
Thanks for reply Marko,
But when i see the data from report server database maximum amount was taken by take stored procedure to excute the data.
i saw some reports as taken almost 2hours 10 mins in that 95% by data reterival by stored procedure and remaining else 5% and in another case 40% by stored procedure and remaining by excuting the report.
Thanks in advance
Well then there’s definetely something wrong with the stored proc. Unless you have gazillion size database of course.
I would do couple of things first. I would start with checking if there’s something fundamentally wrong, something like normal programming problems, large nested loops for example.
Then I’d check that if the procedures are making queries that are missing indexes. Especially non-indexed NOT IN queries and datetime fields can take up a lot of time to finish. In one case I dropped 4 minute query to milliseconds just by adding index to datetime field which was used in ORDER BY.
Then I would just go by hunch. Maybe check execution plan, try tuning advisor, go through the proc line-by-line and poke here and there. Unfortunately I can’t be very specific about this since I’d have to see the proc and have your data to test it.
good morning sir
i have 1 col in salary of employee table
salary
15000
12000
13000
18000
40000
20000
12000
16000
8000
6000
3500
12000
means total col of salary 200 but i want to sum only 100 col
i knew that if i want a that sum of 200 col use select sum(salary) from table name but i want sum only for 100 col
then i don’t know about this question plz sir solve this query
thanks in advance
Please try with this code
Select sum(select top100 col1 from table) from table
Is there any point in creating indices on a UNION ALL view when the tables involved in the UNION already are indexed?
In my case, I have a UNION ALL view involving SELECTs on two tables. One table contains a few hundred thousand rows. The other table contains over a million rows. Both tables are already indexed. Could I gain anything by creating indices on the view?
Hello Sir,
You have created a unique clustered index on ID2 column right?
Will this index be valid for the underlying table also?
If so, we can only have one clustered index per table?
I got confused here, can you help me.