Are subquery is allowed in indexed view

This question is asked by one interviewer, can we use subquery in the indexed view. Our answer is No then he asked why and what is alternate of the sub query.

Indexed views (materialized views) have a strict set of restrictions to ensure the data can be consistently and efficiently maintained by the database engine when changes are made to the underlying base tables. The data for an indexed view is physically stored on disk, similar to a table with a clustered index. 

Indexed views are not just views; they are incrementally maintained tables. Every INSERT / UPDATE / DELETE on a base table must update the indexed view row-by-row in real time. That requires SQL Server to know for this one changed row, exactly which rows in the view change? Subqueries destroy that guarantee.

Let’s see the demo.

Creating a table and inserting view records and also creating view using this base table.

DROP TABLE IF EXISTS dbo.Orders_subdemo; DROP TABLE IF EXISTS dbo.Payments; GO  CREATE TABLE dbo.Orders_subdemo (     OrderID   INT NOT NULL PRIMARY KEY,     OrderDate DATE NOT NULL );  CREATE TABLE dbo.Payments (     PaymentID INT NOT NULL PRIMARY KEY,     OrderID   INT NOT NULL,     Amount    MONEY NOT NULL ); GO  INSERT INTO dbo.Orders_subdemo VALUES (1,'2025-01-01'), (2,'2025-01-01');  INSERT INTO dbo.Payments VALUES (1,1,100), (2,1,50), (3,2,200); GO --- create VIEW dbo.vOrderPaid WITH SCHEMABINDING AS SELECT     o.OrderID,               count_big(*) as cnt,     (SELECT SUM(p.Amount)      FROM dbo.Payments p      WHERE p.OrderID = o.OrderID) AS PaidAmount FROM dbo.Orders_subdemo o group by o.OrderID; GO

Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vOrderPaid ON dbo.vOrderPaid(OrderID); GO

Not able to create index on this view.

Below are some example where we can’t create indexed view.

-- Correlated SELECT * FROM Orders_sub_demo o WHERE o.Amount >       (SELECT AVG(Amount) FROM Orders)  -- Non-correlated SELECT * FROM dbo.Orders_sub_demo WHERE CustomerId IN (SELECT CustomerId FROM VIPCustomers)  -- EXISTS WHERE EXISTS (SELECT 1 FROM Refunds r WHERE r.OrderId = o.OrderId)  -- Scalar subquery SELECT OrderId,        (SELECT SUM(Payment) FROM Payments p WHERE p.OrderId = o.OrderId) FROM Orders_sub_demo o

Alternative of sub query

CREATE VIEW dbo.vOrderPaid2 WITH SCHEMABINDING AS SELECT     o.OrderID,     SUM(p.Amount) AS PaidAmount,     COUNT_BIG(*) AS Cnt FROM dbo.Orders_subdemo o JOIN dbo.Payments p ON o.OrderID = p.OrderID GROUP BY o.OrderID; GO

View created successfully.

Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vOrderPaid2 ON dbo.vOrderPaid2(OrderID); GO

Index is created successfully.

Run this view

Keep in mind: self-join is not allowed in the indexed view.

See below example

CREATE VIEW dbo.vCustomerRunningTotal_demo WITH SCHEMABINDING AS SELECT     o.CustomerID,                  count_big(*) as cnt,     sum(o2.Amount) CustomerTotal FROM dbo.Orders_sub_demo o join dbo.Orders_sub_demo o2 on o2.CustomerID=o.CustomerID group by o.CustomerID GO

View created. Now creating index on this view.

CREATE UNIQUE CLUSTERED INDEX IX_vCustomerRunningTotal_demo ON dbo.vCustomerRunningTotal_demo (CustomerID, CustomerTotal); GO

Getting error.