I am trying to take a table with a parent child relationship and get the number of children. I would like to create an indexed view of the number of children by utilizing COUNT_BIG(*).

The problem is that in my index view I don't want to eliminate the entities who have no children, instead I want the Count to be 0 for those.

Given

> Id | Entity | Parent
> -: | :----- | :-----
>  1 | A      | null  
>  2 | AA     | A     
>  3 | AB     | A     
>  4 | ABA    | AB    
>  5 | ABB    | AB    
>  6 | AAA    | AA    
>  7 | AAB    | AA    
>  8 | AAC    | AA    

I want to create an indexed view that returns

> Entity | Count
> :----- | ----:
> A      |     2
> AA     |     3
> AB     |     2
> ABA    |     0
> ABB    |     0
> AAA    |     0
> AAB    |     0
> AAC    |     0

Here is my SQL that works, but using a LEFT JOIN and a CTE (both are not allowed in an index view)

    DROP TABLE IF EXISTS Example
    CREATE TABLE Example (
      Id INT primary key,
      Entity varchar(50),
      Parent varchar(50)
    )
    
    
    INSERT INTO Example
    VALUES 
       (1, 'A', NULL)
      ,(2, 'AA',  'A')
      ,(3, 'AB','A')
      ,(4, 'ABA', 'AB')
      ,(5, 'ABB', 'AB')
      ,(6, 'AAA', 'AA')
      ,(7, 'AAB', 'AA')
      ,(8, 'AAC', 'AA')
    
    
    
    SELECT *
    FROM Example
    
    ;WITH CTE AS (
     SELECT Parent, COUNT(*) as Count
      FROM dbo.Example
      GROUP BY Parent
    )
      
    SELECT e.Entity, COALESCE(Count,0) Count
    FROM dbo.Example e
    LEFT JOIN CTE g
    ON e.Entity = g.Parent


GO

• db<>fiddle demo

I don't think you can achieve that using a CTE neither a LEFT JOIN because there are many restriction using the indexed views.

Workaround

I suggest splitting the query into two part:

1. Create an indexed view instead of a common table expression (CTE)
2. Create a non indexed view that perform the LEFT JOIN

Beside that, create a Non-Clustered index on Entity column in Table Example .

Then when you query the non-indexed view, it will use indexes

--CREATE TABLE
CREATE TABLE Example (
  Id INT primary key,
  Entity varchar(50),
  Parent varchar(50)
)

--INSERT VALUES
INSERT INTO Example
VALUES 
   (1, 'A', NULL)
  ,(2, 'AA',  'A')
  ,(3, 'AB','A')
  ,(4, 'ABA', 'AB')
  ,(5, 'ABB', 'AB')
  ,(6, 'AAA', 'AA')
  ,(7, 'AAB', 'AA')
  ,(8, 'AAC', 'AA')

--CREATE NON CLUSTERED INDEX
CREATE NONCLUSTERED INDEX idx1 ON dbo.Example(Entity);

--CREATE Indexed View

CREATE VIEW dbo.ExampleView_1
    WITH SCHEMABINDING
    AS 
 SELECT Parent, COUNT_BIG(*) as Count
  FROM dbo.Example
  GROUP BY Parent

CREATE UNIQUE CLUSTERED INDEX idx ON dbo.ExampleView_1(Parent);

--Create non-indexed view
CREATE VIEW dbo.ExampleView_2
    WITH SCHEMABINDING
    AS 
    SELECT e.Entity, COALESCE(Count,0) Count
    FROM dbo.Example e
    LEFT JOIN dbo.ExampleView_1 g
    ON e.Entity = g.Parent

So when you perform the following query:

SELECT * FROM dbo.ExampleView_2 WHERE Entity = 'A'

You can see that the view Clustered index and the Table Non-Clustered index are used in the execution plan:

Additional Information

I didn't find additional workarounds to replace the use of LEFT JOIN or UNION or CTE in indexed views, you can check many similar Stackoverflow questions:

• Indexing views with a CTE
• What to replace left join in a view so i can have an indexed view?
• Create an index on SQL view with UNION operators? Will it really improve performance?

---

Update 1 - Splitting view vs. Cartesian join

To identify the better approach, i tried to compare both suggested approaches.

--The other approach (cartesian join)
CREATE TABLE TwoRows (
    N INT primary key
)

INSERT INTO TwoRows
VALUES (1),(2)

CREATE VIEW dbo.indexedView  WITH SCHEMABINDING AS
    SELECT 
        IIF(T.N = 2, Entity, Parent) as Entity
        , COUNT_BIG(*) as CountPlusOne
        , COUNT_BIG(ALL IIF(T.N = 2, NULL, 1)) as Count
    FROM dbo.Example E1
    INNER JOIN dbo.TwoRows T
        ON 1=1
    WHERE IIF(T.N = 2, Entity, Parent) IS NOT NULL
    GROUP BY IIF(T.N = 2, Entity, Parent)
GO

CREATE UNIQUE CLUSTERED INDEX testIndex ON indexedView(Entity)

I created each indexed view on seperate databases and performed the following query:

SELECT * FROM View WHERE Entity = 'AA'

Splitting view

Cartesian Join

Time Statistics

The time statistics shows that the Cartesian join approach execution time is higher than the Splitting view approach, as shown in the image below (cartesian join to the right):

Adding WITH(NOEXPAND)

Also i tried to add WITH(NOEXPAND) option the the cartesian join approach, to force the database engine to use the indexed view clustered index and the result was as following:

I cleared all caches and perform a comparison, the Time statistics comparison shows that the Splitting view approach is still faster than the cartesian join approach (WITH(NOEXPAND) approach to the right):