September 17, 2014 at 3:39 am
Hi folks,
i'm building a view for which i would use fulltext search. To use fulltext search the view needs to be schemabinded and an unique clustered index. And there is my problem.
With the following code i created my view:
create view [dbo].[CONTRACT_INFORMATION]
with schemabinding
as
select
contracts.nvirtnr as contracts_nvirtnr
, contracts.cpolnn as contracts_cpolnn
, contracts.csuchpolnn as contracts_csuchpolnn
, contracts.cdepotnr as contracts_cdepotnr
, contracts.csuchdepot as contracts_csuchdepotnr
, contracts.crefnr as contracts_crefnr
, contracts.mrisiko as contracts_mrisiko
, contracts.dbeginn as contracts_dbeginn
, contracts.ynetto as contracts_ynetto
, contracts.izahlw as contracts_izahlw
, contracts.nemployee as contracts_nemployee
, contracts.ncompany as contracts_ncompany
, contracts.lprovmod as contracts_lprovmod
, contracts.lumsaktiv as contracts_lumsaktiv
, contracts.lviewedcontract as contracts_lviewedcontract
, contracts.lofferte as contracts_lofferte
, contracts.leigen as contracts_leigen
, contracts.cinaktiv as contracts_cinaktiv
, contracts.nprodukt as contracts_nprodukt
, company.cmatch as company_cmatch
, e_person.cname as employee_person_cname
, e_person.cvorname as employee_person_cvorname
, employee.cmanr as employee_cmanr
, c_person.cname as c_person_cname
, c_person.czeile2 as c_person_czeile2
, c_person.cnamezus as c_person_cnamezus
, customer_person.cname as customer_person_cname
, customer_person.cvorname as customer_person_cvorname
, customer_person.czeile2 as customer_person_czeile2
, customer_person.cnamezus as customer_person_cnamezus
, customer.cknr as customer_cknr
, customer.nemployee as customer_nemployee
, customer.nvirtnr as customer_nvirtnr
from dbo.contracts
join dbo.customer on contracts.ncustomer = customer.nvirtnr
join dbo.person customer_person on customer_person.nvirtnr = customer.nperson
join dbo.employee on contracts.nemployee = employee.nvirtnr
join dbo.person e_person on e_person.nvirtnr = employee.nperson
join dbo.company on contracts.ncompany = company.nvirtnr
join dbo.person c_person on company.nperson = c_person.nvirtnr
When i try to create the index SQL Server tells me that the index couldn't be created because of a self join on table person.
A self join in my perception is something like
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field
A join between the same table with different aliases. In my view there are multiple joins on table person but everyone from another table to person, not from person as P1 on person as P2.
Could anyone explain why SQL Server identifies this a self join? And give me a hint to avoid this trap :w00t:
Thanks for replies
Michael
September 17, 2014 at 7:07 am
...
When i try to create the index SQL Server tells me that the index couldn't be created because of a self join on table person.
...
A self join in my perception is something like ....
A join between the same table with different aliases. In my view there are multiple joins on table person but everyone from another table to person, not from person as P1 on person as P2.
...
You kind of right, as classical definition of self join is (SQL Server BoL ) "a result set that joins records in a table with other records in the same table."
So, you may read it as table should join to itself.
However, you can also read it as "records from a table + related records from the same table" where relashionship might be directly defined or defined via intermidiate table...
Not very clear isn't it!
As, for the index creation error, I think, it's not a correct description... Simply, SQL Server does see the same table is used twice in the query with different alliases and that is good enough reason for it to stop you creating indexed view (due to complexity of index maitenance when this table is updated).
You may want to read http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cc8b3e0e-6eab-412d-b4c8-53dd5c32f686/cannot-create-index-on-the-view-the-view-contains-a-self-join?forum=sqldatabaseengine
September 17, 2014 at 11:51 pm
Obviously SQL Server interprets multiple JOINs to same table as a SELF JOIN. Interesting - but for my purpose not really usefull :crazy:
After playing around a bit with different approaches i decided to provide the data needed for the view in some crosstables. Seems to give more performance instead of nested views.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply