SQL SERVER – Index Created on View not Used Often – Observation of the View – Part 2

Earlier, I have written an article about SQL SERVER – Index Created on View not Used Often – Observation of the View. I received an email from one of the readers, asking if there would no problems when we create the Index on the base table.

Well, we need to discuss this situation in two different cases.

Before proceeding to the discussion, I strongly suggest you read my earlier articles. To avoid the duplication, I am not going to repeat the code and explanation over here.

In all the earlier cases, I have explained in detail how Index created on the View is not utilized.

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

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

SQL SERVER – Indexed View always Use Index on Table

As per earlier blog posts, so far we have done the following:

  • Create a Table
  • Create a View
  • Create Index On View
  • Write SELECT with ORDER BY on View

However, the blog reader who emailed me suggests the extension of the said logic, which is as follows:

  • Create a Table
  • Create a View
  • Create Index On View
  • Write SELECT with ORDER BY on View
  • Create Index on the Base Table
  • Write SELECT with ORDER BY on View

After doing the last two steps, the question is “Will the query on the View utilize the Index on the View, or will it still use the Index of the base table?

Let us first run the Create example.

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
ORDER BY ID2
GO
-- Create Index on Original Table
-- On Column ID1
CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable
(
ID1 ASC
)
GO
-- On Column ID2
CREATE UNIQUE NONCLUSTERED INDEX [IX_OriginalTable_ID2] ON mySampleTable
(
ID2
)
GO
-- Select from view
SELECT ID1,ID2,SomeData
FROM SampleView
ORDER BY ID2
GO

Now let us see the execution plans for both of the SELECT statement.

Before Index on Base Table (with Index on View):

After Index on Base Table (with Index on View):

Looking at both executions, it is very clear that with or without, the View is using Indexes.

Alright, I have written 11 disadvantages of the Views. Now I have written one case where the View is using Indexes. Anybody who says that I am being harsh on Views can say now that I found one place where Index on View can be helpful.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

4 thoughts on “SQL SERVER – Index Created on View not Used Often – Observation of the View – Part 2

  1. Hi,

    After Creating index on base table in same sequence,refresh view then you get same result as before.
    It still use the Index of the base table?

    Like

  2. On modifying non clustered index on base table and converting it into covering index as follow

    CREATE UNIQUE NONCLUSTERED INDEX [IX_OriginalTable_ID2] ON [dbo].[mySampleTable] ( [ID2] ASC )
    INCLUDE ( [ID1], [SomeData] )
    ON [PRIMARY]

    Query optimizer again using this NON CLUSTERED index instead of clustered index on view
    WHY query optimizer prefer to use this NC index instead of Clustered index with same structure on view ???

    Like

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

  4. Hi
    i have a one doubt on idexes that is does we create a view on index table ? after creating view on index again does we create index on view indexed table
    give me the reply as soon as possible

    thanking you
    mohan

    Like

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