Create indexed view with self join

Question

I would like create indexed view (on MSSQL 2008 R2) for task: Get list of players, which can control heroes.

• Player (tblPlayer) has 0-N heroes (tblBattleTarget + tblHero).
• Player can be in 0-N clans (tblMembershipPlayer2PlayerClan).

• Player can share heroes with other players in same clan (tblHero.Sharing = 2).

• Hero can be controled by owner or by other players in clans where is owner a member and hero is enabled to share.

I created query:

SELECT
    H.HeroID /*PK of hero*/
    , BT.IDBattleTargetOwner /*ID of owner of hero (player)*/
    , MP2PC_Other.IDPlayer AS IDOtherPlayerByClan /*ID of another player, which can control hero*/
FROM [dbo].[tblPlayer] AS P_Owner /*owner of heroes*/
INNER JOIN [dbo].[tblBattleTarget] AS BT /*"base class" for hero*/
    ON BT.IDBattleTargetOwner = P_Owner.PlayerID
INNER JOIN [dbo].[tblHero] AS H /*hero in game*/
    ON H.HeroID = BT.BattleTargetID
INNER JOIN [dbo].[tblMembershipPlayer2PlayerClan] AS MP2PC_Owner /*hero's owner can be in 0-N clans*/
    ON MP2PC_Owner.IDPlayer = BT.IDBattleTargetOwner
INNER JOIN [dbo].[tblMembershipPlayer2PlayerClan] AS MP2PC_Other /*other players can be in 0-N clans*/
    ON MP2PC_Other.IDPlayerClan = MP2PC_Owner.IDPlayerClan
WHERE H.Sharing = [dbo].[CONST_Sharing_PlayerClan]() /*only heroes shared with clan can be in result*/

However when I try to create an index on the view, I get error: Cannot create index on view "mydatabase.dbo.vwHero_SharingWithClan". The view contains a self join on "mydatabase.dbo.tblMembershipPlayer2PlayerClan". (Microsoft SQL Server, Error: 1947)

I searched the web & sql server books online and I didn't found way how to workaround the self-join issue. Is there some way how to do that? Or how should I rewrite the query for proper results?

Thanks!

Solution

You can't have a self-join in an indexed view - it's just not possible (I guess the self-join makes updating the index too complicated to perform properly).

See this link Creating an Indexed View with a Self-Join (Kinda) for some hints on how you might be able to re-structure your query to work around this.

Comments

[Damien_The_Unbeliever]

Nice. Hadn't seen this before, and seems to match the OPs problem statement quite well.

[TcKs]

Thanks for link, but all tables in joins are "life" tables. I can't use "CASE WHEN" because I don't know the values.

[Damien_The_Unbeliever]

@TcKs - I'd suggest you re-read the article. The expressions in the WHEN clauses are based on rows from a fixed table of two rows, or on what was previously being used as the join conditions for the two joins to the same table. The table with two rows is a new table to introduce (I'd guess you can replace it with a literal two row union) to the join conditions.

[TcKs]

@ Damien_The_Unbeliever: Ok, I'll re-read the article. If I missed something and found it usefull, I'll happy.