As a SQL Server developer, I recently came across a fascinating observation while working on an optimization project for a large organization. During the project, we discovered that a query on a base table was utilizing an index that did not exist on the table itself. Instead, it was using an index created on a related indexed view of the same base table. This was a unique scenario that I had never encountered before.

Let me explain this concept in simpler terms: “A query on a base table can use an index created on an indexed view of the same base table.” This observation is quite interesting and rare in real-world scenarios.

To better understand this behavior, I decided to recreate it with a small example. Here’s the code:

USE tempdb

-- Create Table
CREATE TABLE IndexViewAggr (
    ID INT
)

-- Insert One Hundred Thousand Records
INSERT INTO IndexViewAggr (ID)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

-- Create View on the table
CREATE VIEW ViewAggr WITH SCHEMABINDING AS
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID

-- Run the query on the base table
-- Please note that in the execution plan there is a Table Scan
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID

-- Create Index on the View
-- Please note that this index is on the View and not on the table
CREATE UNIQUE CLUSTERED INDEX IndexView ON ViewAggr (ID)

-- Run the query on the base table again
-- Please note that in the execution plan there is an Index Scan
SELECT ID, COUNT_BIG(*) CountID
FROM dbo.IndexViewAggr
WHERE ID BETWEEN 1 AND 1000
GROUP BY ID

-- Clean up Database
DROP VIEW ViewAggr
DROP TABLE IndexViewAggr

In this example, we create a table called “IndexViewAggr” and insert 100,000 records into it. We then create a view called “ViewAggr” on the table, which performs an aggregation on the “ID” column. Initially, when we run a query on the base table, it performs a Table Scan, as expected.

However, when we create an index on the view using the “CREATE UNIQUE CLUSTERED INDEX” statement, something interesting happens. When we run the same query on the base table again, the execution plan shows an Index Scan instead of a Table Scan. This indicates that the query is now utilizing the index created on the view.

This behavior can be explained by understanding how the query optimizer works. When it checks for an index on the view, it finds that the execution plan of the query is similar to that of the view. As a result, it decides to use the index on the view for the query on the base table.

It’s important to note that this behavior is not commonly encountered in real-world scenarios. However, it provides valuable insight into the inner workings of SQL Server indexing and query optimization.

I hope you found this article informative and thought-provoking. Feel free to share your thoughts and experiences with this concept. While it may be slightly complicated, understanding these nuances can greatly enhance your SQL Server development skills.