Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


problem creating index on view


problem creating index on view

Author
Message
Naveen Kumar-807681
Naveen Kumar-807681
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 78
Hi everybody,

I have created a view from another view.

The create statement is:

CREATE VIEW [dbo].[SERVICEDESK_INCIDENTVIEW_TEMP] WITH SCHEMABINDING AS
SELECT Id,State, Title, Description, Resolution, Resp_Viol_Reason, Resol_Viol_Reason,
IncidentType, SourceType_ID, Parent_Incident_ID, FirstCallResolution, CompanyDeleted
FROM DBO.servicedesk_incidentview

The view SERVICEDESK_INCIDENTVIEW_TEMP is created successfully.

I wanted to create a unique clustered index on this view. MY statement is:

create unique clustered index INCIDENT_ID_SERVICEDESK_INCIDENTVIEW_TEMP on SERVICEDESK_INCIDENTVIEW_TEMP(Id)

When I run the above query I got the following error.

Cannot create index on view 'WIPRO_EHELPLINE.dbo.SERVICEDESK_INCIDENTVIEW_TEMP'
because it references another view 'DBO.servicedesk_incidentview'.
Consider expanding referenced view's definition by hand in indexed view definition.

I have performance issue in my application. This view contains more than 5 lakh records.

Please help me how to create unique clustered index on this view. So that my queries performance will increase.

Thanks in advance.

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.Smile
SAT_SQL
SAT_SQL
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 227
Hi sarvan
pls disply ur error correctly.

Pleas verify that shema of table is in correct form ..



Regards
sat
SAT_SQL
SAT_SQL
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 227
Hi

ur error defines definetly problem in schema , please verify that it is retrving the date from one mor schema that which u are not define for ur tables .

Regards
sateesh
Piotr.Rodak
Piotr.Rodak
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 1761
Indexed view must not reference other views, only base tables. You will need to expand definition of the first view in the view you want to index.

Piotr

...and your only reply is slàinte mhath
Naveen Kumar-807681
Naveen Kumar-807681
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 78
I didnt get you. Can u explain me in detail by taking an example.

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.Smile
Naveen Kumar-807681
Naveen Kumar-807681
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 78
both views are in the same schema.

Who is wise? He that learns from everyone. Who is powerful? He that governs his passions. Who is rich? He that is content. Who is that? Nobody.Smile
Piotr.Rodak
Piotr.Rodak
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 1761
The indexed views have a rather long list of limitations regarding their creation. Please refer to Books On Line, Designing And Implementing Views, these are few first lines:

"
A view must meet the following requirements before you can create a clustered index on it:

The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.


The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.


The view must not reference any other views, only base tables.

"

So, indexed view must select from tables, it must not select from other views.

Regards

Piotr

...and your only reply is slàinte mhath
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55865 Visits: 44706
Naveen Kumar (6/10/2009)
I have performance issue in my application. This view contains more than 5 lakh records.

Please help me how to create unique clustered index on this view. So that my queries performance will increase.


Indexing the view may not be the best solution. What are the queries that are slow, what's the view's definition, what are the underlying tables' definitions and what are their indexes? Lastly, can you post the exec plan of the slow queries, saved as .sqlplan files, zipped and attached.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search