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

Question

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 index 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

Astonishing

Answer 1

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).

Gram

So there is no way this VIEW can be rewritten with INNER JOIN to satisfy an indexed view? – Astonishing

@kateroh: I'm afraid that there is none. But why would you need an indexed view for this query? What are you going to index and what to use the index for? – Gram

let's say in this example a query that asks for a list of orders based on Customer Name is the most used function. In the real system this view is composed out of 4 tables. Whereas this query is already optimized with a creation of the view, given that all table joins dont have to happen on each query, it'd be super nice to optimize it further with the unique index on Customer Name (in this case Customer Name is unique for sure). – Astonishing

@kateroh: you can create a separate table for this and maintain it using the triggers on underlying tables. That's essentially what SQL Server would have to do if it allowed an index on such a view. – Gram

Your "when a new record....aggregate value" point was the most compact and friendly explanation of why indexed views pose so many restrictions. Thanks! – Recite

Answer 2

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

Pantin