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):

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

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

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

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 (https://blog.sqlauthority.com)

SQL Scripts, SQL View
Previous Post
SQL SERVER – Public Training and Private Training – Differences and Similarities – Public Training vs Private Training
Next Post
SQLAuthority News – Feature Pack for Microsoft SQL Server 2005 SP4

Related Posts

3 Comments. Leave new

  • 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?

    Reply
  • 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 ???

    Reply
  • 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

    Reply

Leave a Reply