Indexed View with tables referencing to itself

Question

Indexed View with tables referencing to itself

Thinky Night

SSCommitted

Points: 1663
More actions
August 1, 2011 at 10:03 pm

#245387

I have a query but could not change it to a indexed view:
My query is like:
alter view dbo.Query_Test(col1,col2,col3) WITH SCHEMABINDING
select a.col1,b.col2,c.col3
from table1 a
inner join table1 b on a.ID = b.Parent_ID
inner join table2 c on a.ID = c.Custom_ID
create clustered unique index index1 on dbo.Query_test(col1,col2,col3)
But i get this following error:
Cannot create index on view "dbo.Query_Test". The view contains a self join on "dbo.table1".
What do i have to do?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This would be a hack but "MAYBE" it could work. Put a synonym on that table. Reference the table once and then the synonym.

Never tested it, but it just might work. If not then there's nothing you can do AFAIK from the indexed view side.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 2

Thinky Night (8/1/2011)
I have a query but could not change it to a indexed view:
My query is like:
alter view dbo.Query_Test(col1,col2,col3) WITH SCHEMABINDING
select a.col1,b.col2,c.col3
from table1 a
inner join table1 b on a.ID = b.Parent_ID
inner join table2 c on a.ID = c.Custom_ID
create clustered unique index index1 on dbo.Query_test(col1,col2,col3)
But i get this following error:
Cannot create index on view "dbo.Query_Test". The view contains a self join on "dbo.table1".
What do i have to do?

You may not need an indexed view for this. It would appear that you're using the main ID's from 3 tabls which probably already have indexes on them. A normal view is just like any query and it will use the indexes on the underlying tables if they're present and the code is in good order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Good point Jeff, what do you want to get out of the indexed view that a normal view can,t give you?