I use SQL Server 2008 R2 and know that in Indexed View cant have self join.

I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between each record and parent record. can I simulate self join in this view?


My table structure is :

 SAM.Detail (DetailID Int, ParentDetailID Int, Quantity Int, ...)

and my query is :

 Select A.DetailID,
       A.Quantity - SUM(B.Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail A
inner join SAM.Detail B ON B.ParentDetailID = A.DetailID
Group By A.DetailID, A.Quantity

migrated from stackoverflow.com Sep 4 '12 at 12:23

This question came from our site for professional and enthusiast programmers.

  • As of now the sql server does not support creating an index on the views containing self joins. So you can have just view but not indexed view. – AnandPhadke Sep 3 '12 at 7:26
  • 1
    jmkehayias.blogspot.co.uk/2008/12/… should work in this case I think as each row has at most one parent. You need to double up the rows then pivot out parent and child. – Martin Smith Sep 3 '12 at 7:42
  • But having read that in more detail it only gets you part of the way. MIN/MAX and PIVOT are not allowed in indexed views so the pivoted view can't itself be indexed. Probably not worth doing. – Martin Smith Sep 3 '12 at 8:01
  • 1
    could you provide table definition, query and expected result? Please explain why you currently want indexed views. (adn what you want indexed). Somebody might be able to provide you with a alternative. – Edward Dortland Sep 4 '12 at 16:44
  • @Edward, Please see my edit. – Morteza Yarahmadi Sep 5 '12 at 8:18

If all you need is to speed up your query, you can create the following indexed view:

Select ParentDetailID ,
       SUM(Quantity) as RemainQuantity,
       COUNT_BIG(*) as CountBig
From SAM.Detail 
Group By ParentDetailID 

You can join it you Detail table, and your query might run faster.


I personally suggest you can look through what is left join , inner join and right join

You can read the details by searching the related keyworkds or go to thi following webpage(s)



In this case, right join is more suitable if your child record must have parent ID and all parent ID is not null

The Child record having null parent ID or parent record have no child cannot be displayed under inner join .

Note that if the right join joining the self table continues forever , the tree structure simulating the structures assigning file folders like Windows or Mac OSX can be simulated.

  • 3
    The question is about how to get around the various restrictions on join types in SQL Server indexed views not joins in general. – Martin Smith Oct 2 '12 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.