Views are a commonly used concept in SQL Server, but there are often misconceptions about their limitations and how they interact with indexes. In a previous article, I discussed the limitations of views and demonstrated that queries on views without an index on the underlying table do not utilize the view. In this article, I will explore the scenario where an index on a view can be utilized.

Let’s walk through the steps to see how an index created on a view can be utilized:

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

Here is an example of how these steps can be implemented:

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

When we check the execution plan for this query, we can see that the index created on the view is utilized. The ORDER BY clause uses the index, resulting in improved performance. This demonstrates how an index on a view can be beneficial.

If you’re interested in learning more about the limitations of views, I recommend reading my earlier series on the topic: “SQL SERVER – The Limitations of the Views – Eleven and more…”. Understanding these limitations can help you make informed decisions when working with views in your SQL Server environment.

Thank you for reading and stay tuned for more articles on SQL Server concepts and best practices!