current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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?

EDIT

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
share|improve this question
    
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
show 1 more comment

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

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

2 Answers

up vote 1 down vote

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.

share|improve this answer
add comment
up vote -2 down vote

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)

http://www.w3schools.com/sql/sql_join_right.asp

http://stackoverflow.com/questions/2647150/outer-join-for-parent-child-chain

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.

share|improve this answer
1  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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