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:
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
-- 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),
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
-- Create View
CREATE VIEW SampleView
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
-- Select from view
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)