SQL SERVER – Index Created on View not Used Often – Limitation of the View 12

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)

About these ads

7 thoughts on “SQL SERVER – Index Created on View not Used Often – Limitation of the View 12

  1. 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.

    Like

  2. Pingback: SQL SERVER – Index Created on View not Used Often – Observation of the View Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – Index Created on View not Used Often – Observation of the View – Part 2 Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s