June 10, 2009 at 6:21 am
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.:)
June 10, 2009 at 7:18 am
Hi sarvan
pls disply ur error correctly.
Pleas verify that shema of table is in correct form ..
Regards
sat
June 10, 2009 at 7:21 am
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
June 10, 2009 at 8:04 am
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
June 10, 2009 at 8:34 am
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.:)
June 10, 2009 at 8:35 am
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.:)
June 10, 2009 at 9:34 am
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
June 10, 2009 at 10:13 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply