I have previously written on the subject SQL SERVER – The Limitations of the Views – Eleven and more…. This was indeed a very popular series and I had received lots of feedback on that topic. Today we are going to discuss something very interesting as well.
During my recent performance tuning seminar in Hyderabad, I presented on the subject of Views. During the seminar, one of the attendees asked a question:
We create a table and create a View on the top of it. On the same view, if we create Index, when querying View, will that index be used?
The answer is NOT Always!
(There is only one specific condition when it will be used. We will write about that later in the next post).
Let us see the test case for the same. In our script we will do following:
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 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
Let us check the execution plan for the last SELECT statement.
You can see from the execution plan. That even though we are querying View and the View has index, it is not really using that index.
In the next post, we will see the significance of this View and where it can be helpful.
Meanwhile, I encourage you to read my View series: SQL SERVER – The Limitations of the Views – Eleven and more….
Reference: Pinal Dave (http://blog.sqlauthority.com)
omg it is crazy how you manage to twist the test scenario so that it proves your weird point. why would you use a materialized view in the first place? the query does not fit the index at all, too. it is just of no benefit.
It reminds me one of your early (nice) post http://blog.sqlauthority.com/2010/10/12/sql-server-indexed-view-always-use-index-on-index/
[...] the query on the View will not use the View. You can read about this demonstration over here: SQL SERVER – Index Created on View not Used Often – Limitation of the View 12. I promised in that post that I would also write an article where I would demonstrate the condition [...]
[...] SQL SERVER – Index Created on View not Used Often – Limitation of the View 12 [...]
[...] Size of Index Table for Each Index – Solution 2 Computed Columns – Index and Performance Index Created on View not Used Often – Limitation of the View 12 Video – Performance Improvement in Columnstore [...]
sir i need to knw can wee create an index over view in sql server 2005 if its possible how plz let me know that
[...] Index Created on View not Used Often – Limitation of the View 12 This was a follow up of my earlier series of Limitation of the Views. Index are great when used properly – they are like a silver bullet. The same is true for Views as well. However, if both of them are together and not used properly that is the recipe of the disaster. Here is the earlier series of 11 limitations. [...]