Earlier, we discussed the limitations of creating an index on a view in SQL Server. However, a reader recently asked if there would be any problems when creating an index on the base table. In order to address this question, we need to consider two different scenarios.

In my previous articles, I explained in detail how an index created on a view is not utilized effectively. If you haven’t read those articles yet, I strongly recommend doing so to gain a better understanding of the topic.

In this blog post, we will extend the logic discussed in the previous articles. We will perform the following steps:

1. Create a table
2. Create a view
3. Create an index on the view
4. Write a SELECT statement with ORDER BY on the view
5. Create an index on the base table
6. Write a SELECT statement with ORDER BY on the view

After completing these steps, the question arises: “Will the query on the view utilize the index on the view, or will it still use the index of the base table?”

Let’s run through an example to find out:

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’s examine the execution plans for both SELECT statements.

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

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

Looking at both execution plans, it is clear that the view is utilizing indexes regardless of whether an index is present on the base table or not.

So, in this particular scenario, we have found a case where an index on a view can be helpful. It is important to note that this may not always be the case, as the effectiveness of an index on a view depends on various factors such as the complexity of the view and the nature of the queries being executed.

Stay tuned for more articles on SQL Server concepts and best practices!