clustered index on view prevented

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

clustered index on view prevented

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-28 : 13:46:03

I am being prevented creating a clustered index on a view because of the self/outer join restriction.

My query has a inner join and two left joins (one of the left joins is a self join).
If I take the explicit join statements out and comma seperate the tables in the from clause and put the "ON" part in the WHERE clause, that cures the inner join complaint but not the self join issue.
If I understand it correctly, by doing this I also lose the left join functionality - so potencially I could be returning less records.

How can I get around this so that I can create the clustered index on the view?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-28 : 16:56:15

How about posting some T-SQL for us?

This article might help detail the restrictions of an indexed view:

http://www.sqlteam.com/item.asp?ItemID=1015

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-29 : 04:07:23

SELECT dbo.Companies.COM_CompanyID AS CompanyGuid, dbo.Companies.COM_Name AS CompanyName, dbo.Addresses.ADD_Town AS Town,
dbo.Companies.COM_ParentID AS ParentGuid, dbo.Companies.COM_CompanyTypeEnum AS CompanyType,
dbo.Companies.COM_RulesActive AS RulesActive, dbo.Companies.COM_Archived AS Archived, CASE WHEN (Clients.CLI_ClientTypeEnum > 0 AND
Clients.CLI_ClientTypeEnum < 254) THEN 1 ELSE 0 END AS Managed, CASE WHEN ChildCompanies.COM_ParentID IS NULL
THEN 0 ELSE 1 END AS HasChildren
FROM dbo.Companies INNER JOIN
dbo.Clients ON dbo.Companies.COM_CompanyID = dbo.Clients.CLI_CompanyID LEFT JOIN
dbo.Companies ChildCompanies ON ChildCompanies.COM_ParentID = dbo.Companies.COM_CompanyID LEFT JOIN
dbo.Addresses ON dbo.Companies.COM_CompanyID = dbo.Addresses.ADD_ParentID AND dbo.Addresses.ADD_AddTypeEnum = 0
WHERE (dbo.Companies.COM_Name IS NOT NULL) AND (dbo.Companies.COM_CompanyTypeEnum < 254)

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-29 : 06:31:10

I think I understand all of this: http://www.sqlteam.com/item.asp?ItemID=1015

But I still cant see why the inner join and the two left joins would prevent the clustered index on the view

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-29 : 06:47:19

I know the two left joins are causing the problem. How can I maintain the same functionality of a left join (but without using a left join) so that I can make a clustered index on the view?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources