title: Need to convert a recursive CTE query to an index friendly query
body: <p>After going through all the hard work of writing a recursive CTE query to meet my needs, I realize I can't use it because it doesn't work in an indexed view.  So I need something else to replace the CTE below.  (Yes you can use a CTE in a non-indexed view, but that's too slow for me).</p>
  
  <p><strong>The requirements:</strong></p>
  
  <ol>
  <li><p>My ultimate goal is to have a self updating indexed view (it doesn't have to be a view, but something similar)... that is, if data changes in any of the tables the view joins on, then the view needs to update itself.  </p></li>
  <li><p>The view needs to be indexed because it has to be very fast, and the data doesn't change very frequently.  Unfortunately, the non-indexed view using a CTE takes 3-5 seconds to run which is way too long for my needs.  I need the query to run in milliseconds.  The recursive table has a few hundred thousand records in it.</p></li>
  </ol>
  
  <p>As far as my research has taken me, the best solution to meet all these requirements is an indexed view, but I'm open to any solution.</p>
  
  <p>The CTE can be found in the answer to my <a href="https://stackoverflow.com/questions/4720012/help-with-recursive-cte-query-joining-to-a-second-table">other post</a>.
  Or here it is again:</p>
  
  <pre><code>DECLARE @tbl TABLE ( 
       Id INT 
      ,[Name] VARCHAR(20) 
      ,ParentId INT 
      ) 
  
  INSERT INTO @tbl( Id, Name, ParentId ) 
  VALUES 
   (1, 'Europe', NULL) 
  ,(2, 'Asia',   NULL) 
  ,(3, 'Germany', 1) 
  ,(4, 'UK',      1) 
  ,(5, 'China',   2) 
  ,(6, 'India',   2) 
  ,(7, 'Scotland', 4) 
  ,(8, 'Edinburgh', 7) 
  ,(9, 'Leith', 8) 
  
  ; 
  DECLARE @tbl2 table (id int, abbreviation varchar(10), tbl_id int)
  INSERT INTO @tbl2( Id, Abbreviation, tbl_id ) 
  VALUES 
   (100, 'EU', 1) 
  ,(101, 'AS', 2) 
  ,(102, 'DE', 3) 
  ,(103, 'CN', 5)
  
  ;WITH abbr AS (
      SELECT a.*, isnull(b.abbreviation,'') abbreviation
      FROM @tbl a
      left join @tbl2 b on a.Id = b.tbl_id
  ), abcd AS ( 
            -- anchor 
          SELECT  id, [Name], ParentID,
                  CAST(([Name]) AS VARCHAR(1000)) [Path],
                  cast(abbreviation as varchar(max)) abbreviation
          FROM    abbr
          WHERE   ParentId IS NULL 
          UNION ALL
            --recursive member 
          SELECT  t.id, t.[Name], t.ParentID, 
                  CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) [Path],
                  isnull(nullif(t.abbreviation,'')+',', '') + a.abbreviation
          FROM    abbr AS t 
                  JOIN abcd AS a 
                    ON t.ParentId = a.id 
         )
  SELECT *, [Path] + ':' + abbreviation
  FROM abcd
  </code></pre>