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. Let us learn about the issue of index created on view on used often.
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 an 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 (https://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 https://blog.sqlauthority.com/2010/10/12/sql-server-indexed-view-always-use-index-on-index/
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