title: How to create indexed view of children count
body: <p>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 <code>COUNT_BIG(*)</code>.</p>
  <p>The problem is that in my index view I don't want to eliminate the entities who have no children, instead I want the <code>Count</code> to be 0 for those.</p>
  <p>Given</p>
  <pre><code>&gt; Id | Entity | Parent
  &gt; -: | :----- | :-----
  &gt;  1 | A      | null  
  &gt;  2 | AA     | A     
  &gt;  3 | AB     | A     
  &gt;  4 | ABA    | AB    
  &gt;  5 | ABB    | AB    
  &gt;  6 | AAA    | AA    
  &gt;  7 | AAB    | AA    
  &gt;  8 | AAC    | AA    
  </code></pre>
  <p>I want to create an indexed view that returns</p>
  <pre><code>&gt; Entity | Count
  &gt; :----- | ----:
  &gt; A      |     2
  &gt; AA     |     3
  &gt; AB     |     2
  &gt; ABA    |     0
  &gt; ABB    |     0
  &gt; AAA    |     0
  &gt; AAB    |     0
  &gt; AAC    |     0
  </code></pre>
  <p>Here is my SQL that works, but using a LEFT JOIN and a CTE (both are not allowed in an index view)</p>
  
  <blockquote>
  <pre><code>    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
  </code></pre>
  </blockquote>
  <ul>
  <li><a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=e2b666ff60e6d37c8bd3752611fd8a02" rel="nofollow noreferrer">db&lt;&gt;fiddle demo</a></li>
  </ul>