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

On a separate thread I got a working example on how to translate my stored proc to a view, that will hold customer names to orders mapping, where orders are comma-separated lists of orders, including NULL for no orders. So for the table below, I need the following to appear in the view:

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

I need to i开发者_StackOverflow社区ndex the view, but you cant do it if the SELECT query within a view has APPLY and/or subqueries. Is it possible to translate this view to 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

You can't make this view indexed.

Basically, you are having an aggregate function here (disguised as CROSS APPLY).

The only aggregate functions allowed in an indexed view are COUNT_BIG and SUM, because they distribute over set addition and subtraction, that is 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 maintainable.

When a new record is inserted, updated or deleted from the underlying table, the whole view does not need to be reevaluated: the value of the new record is just added or subtracted from the aggregate value.

In addition, a COUNT_BIG should be a part of the view as well, to track deletions of records (when it becomes 0, a record should be deleted from the view index).

If you use an inner join with 1=1 it will satisfy the condition, and allow the join.

Select * From x inner join y on 1=1