Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

problem creating index on view Expand / Collapse
Author
Message
Posted Wednesday, June 10, 2009 6:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 14, 2012 2:54 AM
Points: 162, 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.:)
Post #732172
Posted Wednesday, June 10, 2009 7:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 12:04 AM
Points: 91, Visits: 227
Hi sarvan
pls disply ur error correctly.

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



Regards
sat
Post #732241
Posted Wednesday, June 10, 2009 7:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 12:04 AM
Points: 91, 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
Post #732247
Posted Wednesday, June 10, 2009 8:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
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
Post #732314
Posted Wednesday, June 10, 2009 8:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 14, 2012 2:54 AM
Points: 162, 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.:)
Post #732344
Posted Wednesday, June 10, 2009 8:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 14, 2012 2:54 AM
Points: 162, 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.:)
Post #732347
Posted Wednesday, June 10, 2009 9:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
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
Post #732397
Posted Wednesday, June 10, 2009 10:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 41,512, Visits: 34,428
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 2008, MVP
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

Post #732436
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse