How to rewrite CROSS APPLY to INNER JOIN to make the view indexed

Question

In a separate thread, I got a working example on how to translate my saved proc into a view that will contain customer names for matching orders, where orders are commas - separated lists of orders, including NULL without orders. Therefore, for the table below, I need the following to be displayed in the view:

Name Orders 'John' New Hat, New Book, New Phone 'Marry' NULL 

I need to index the view, but you cannot do this if the SELECT query in the view has APPLY and / or subqueries. Can this view be translated into an indexed view?

 create table Customers (CustomerId int, CustomerName VARCHAR(100)) create table Orders (CustomerId int, OrderName VARCHAR(100)) insert into Customers (CustomerId, CustomerName) select 1, 'John' union all select 2, 'Marry' insert into Orders (CustomerId, OrderName) select 1, 'New Hat' union all select 1, 'New Book' union all select 1, 'New Phone' go create view OrderView as select c.CustomerName, x.OrderNames from Customers c cross apply (select stuff((select ',' + OrderName from Orders o where o.CustomerId = c.CustomerId for xml path('')),1,1,'') as OrderNames) x go 

Answer 1

You cannot make this view indexed.

Basically, here you have a summary function (disguised as CROSS APPLY ).

The only aggregate functions allowed in the indexed view are COUNT_BIG and SUM , because they are distributed by addition and subtraction of the set, i.e. SUM(a UNION ALL b) = SUM(a) + SUM(b) , SUM(a EXCEPT ALL b) = SUM(a) - SUM(b) .

This property is required for the index to be supported.

When a new record is inserted, updated, or deleted from the base table, the entire review does not need to be overestimated: the value of the new record is simply added or subtracted from the cumulative value.

In addition, a COUNT_BIG must also be part of the view in order to track the deletion of records (when it becomes 0 , the record must be deleted from the view index).