Unique Clustered Index on View - LEFT JOIN

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

SQL Server Development (2000)

Unique Clustered Index on View - LEFT JOIN

Author

Topic

atomz4peace
Starting Member

7 Posts

Posted - 2007-07-25 : 19:51:14

Hi,

I'm trying to create an indexed view. I have a few others and they work fine, so I know that my SET parameters are correct. I have the following view:

create view dbo.review_template_view
with schemabinding
as
select dbo.review_template_approvers.rev_app_id, dbo.users.manager_id,
dbo.applications_owners.owner_id, dbo.review_template_approvers.custom_approver_id
from dbo.review_template_approvers
left join dbo.users on dbo.users.myid = dbo.review_template_approvers.myid
left join dbo.applications_owners on dbo.applications_owners.application_id = dbo.review_template_approvers.application_id
left join dbo.review_template on dbo.review_template.reviewid = dbo.review_template_approvers.reviewid
where dbo.users.myid = dbo.review_template_approvers.myid and
dbo.review_template.curr_phase = dbo.review_template_approvers.phase

And this works fine. I then try to add an index like:

create unique clustered index review_template_view_revid on review_template_view (rev_app_id)

and I get the dreaded "It contains one or more disallowed constructs." error.

If I remove the LEFT and make them just JOINs, I can create the index. Isn't LEFT JOIN supposed to be OK? All of the columns are integers and all of the _id columns are the PK of the respective table.

Any ideas what to try?

Thanks!
Jim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-25 : 21:58:04

Why do you feel this view needs an index on that column? It comes direct from a table -- just index the column in the table. Indexes in views are usually only useful on expressions.

- Jeff
http://weblogs.sqlteam.com/JeffS

atomz4peace
Starting Member

7 Posts

Posted - 2007-07-26 : 01:00:22

You're right. That column really does not. I was trying to create an indexed view for performance reasons, so that the view itself would be materialized and not computed dynamically at run time. The view will have a large amount of selects on all of the other 3 columns. But the only column that could be unique clustered was reviewid.

So my plan was to then put a normal index on other 3 columns.

Am I thinking down the right road or what is a better way? This view will have tens of thousands of rows. I will do lots of queries like:

select reviewid from view where manager_id = X
select reviewid from view where owner_id = Y
select reviewid from view where approver_id = Z

and/or combinations of the above, sometimes all 3. If I have 10,000 rows, I will have many where I get a few hundred reviewids. But I might also have some where I'll get 50-100% of the table so sometimes the index would not even be used.

Does anyone think I even need an indexed view (unique clustered) or even any indexes at all?

Thanks!
Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources