Index on a view not working.. - SQL Server Forums

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)

Index on a view not working..

Author

Topic

Phil hehir
Starting Member

2 Posts

Posted - 2006-02-07 : 21:29:09

The usual Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'ARS.dbo.UNZ_calls'. It contains one or more disallowed constructs.

I can't see the wood for the trees and cannot see anything wrong with this view or any disallowed constructs?

Can someone please cast a quick eye over it and show me the error of my ways. (it's ugly I know)

Thanks
Phil.

CREATE VIEW dbo.UNZ_calls WITH SCHEMABINDING
AS
SELECT CAST(dbo.calls.CALL_ID AS nvarchar(60)) AS CALL_ID, CAST(dbo.calls.CREATED_BY AS NVARCHAR(50)) AS CREATED_BY, DATEADD(second,
dbo.calls.CREATE_DATE, '01/01/1970 12:00') AS CREATE_DATE, CONVERT(smalldatetime, LEFT(CONVERT(smalldatetime, DATEADD(second,
dbo.calls.CREATE_DATE, '01/01/1970 12:00'), 103), 11)) AS CREATE_DATE2, MONTH(DATEADD(second, dbo.calls.CREATE_DATE, '01/01/1970 12:00'))
AS CREATE_MONTH, YEAR(DATEADD(second, dbo.calls.CREATE_DATE, '01/01/1970 12:00')) AS CREATE_YEAR, dbo.calls.ASSIGNED_TO,
dbo.calls.LAST_MODIFIED_BY, DATEADD(second, dbo.calls.MODIFIED_DATE, '01/01/1970 13:00') AS MODIFIED_DATE,
dbo.calls.RESOLUTION_OR_ACTION, dbo.calls.CALL_VIA, dbo.calls.TOTAL_TIME__MIN_, dbo.calls.SUMMARY, dbo.calls.REPORTING_IDENTIFIER,
dbo.calls.CREATE_SITE_ID, dbo.SCCOMPANY.COMPANY_FULL_NAME AS CUSTOMER_NAME, dbo.calls.CUSTOMER_NAME AS CUSTOMER_CODE,
dbo.calls.CUSTOMER_ID AS IA_, dbo.calls.FIRST_NAME + ' ' + dbo.calls.LAST_NAME AS CALLER_NAME, dbo.calls.PHONE, dbo.calls.PRODUCT,
dbo.calls.CALL_TYPE, dbo.calls.CLOSE_TYPE, dbo.calls.MENU_TYPE_TO_USE, dbo.calls.CALL_STATUS, dbo.calls.RELATED_CALL_ID,
dbo.calls.CALL_PRIORITY, dbo.calls.USER_PRIORITY, dbo.calls.UNISYS_EMPLOYEE, dbo.calls.CALL_CLOSURE, dbo.calls.RESOLUTION,
dbo.calls.CLOSED_BY, dbo.calls.MET_SLA, dbo.calls.CLOSE_DATE, dbo.calls.REPORTABLE_, dbo.calls.LAST_MODIFY_USER, dbo.calls.MULTI_USE3,
dbo.calls.PARENT_CHILD_, dbo.calls.PARENT_CALL_ID, DATEADD(second, dbo.calls.CLOSE_DATE, CONVERT(DATETIME, '1970-01-01 12:00:00', 102))
AS REAL_CLOSE_DATE, CONVERT(smalldatetime, LEFT(CONVERT(smalldatetime, DATEADD(second, dbo.calls.CLOSE_DATE, '01/01/1970 12:00'), 103),
11)) AS REAL_CLOSE_DATE2, dbo.calls.CREATE_DATE AS UNIX_CREATE_DATE, dbo.calls.MULTI_USE4, dbo.calls.MULTI_USE1, dbo.calls.SITE_ID,
DATEADD(second, dbo.calls.DATE_TIME_MULTI_USE2, CONVERT(DATETIME, '1970-01-01 12:00:00', 102)) AS DATE_TIME_MULTI_USE2,
dbo.calls.OTHERSYSID, dbo.calls.VIP, dbo.calls.A_STATE, dbo.calls.CREDIT_CARD_PAYMENT AS FIVE_USERS,
dbo.calls.OTHERSYSID AS REASON_FIVE, dbo.calls.ASSET_TAG AS LANE_NUMBER, dbo.calls.FAX AS RA_Refused, dbo.calls.OTHERSYSREFNO,
dbo.calls.IT_O_NODE_NAME AS Device_Name, dbo.calls.CALL_OPEN_TIME__MIN_, dbo.calls.ELAPSED_TIME__MIN_, dbo.calls.MULTI_USE2,
dbo.calls.A_ADDRESS1 AS ADDRESS1, dbo.calls.A_ADDRESS2 AS ADDRESS2, dbo.calls.A_CITY AS CITY, dbo.calls.ENTITLEMENT_ID_,
dbo.calls.CUIC_NO, dbo.calls.UIS_LOCATION_NAME, dbo.calls.UIS_HELPDESK_RESOLVABLE, dbo.calls.Resolution_Analysis, dbo.calls.FCF,
dbo.calls.NEW, dbo.calls.RESPONDED, dbo.calls.RESOLVED, dbo.calls.RESPONDED_DATE, dbo.calls.PENDING_DATE, dbo.calls.RESOLVED_DATE,
dbo.calls.Support_Information, dbo.calls.Department, dbo.calls.UIS_FIELD_1, dbo.calls.UIS_FIELD_2, dbo.calls.UIS_FIELD_3, dbo.calls.UIS_FIELD_8,
dbo.calls.UIS_FIELD_9, dbo.calls.UIS_FIELD_10, dbo.calls.UIS_FIELD_11, dbo.calls.UIS_FIELD_15, dbo.calls.UIS_FIELD_16,
dbo.calls.CUSTOMER_ID, dbo.calls.MULTI_USE_MENU4 AS OWNER
FROM dbo.calls LEFT OUTER JOIN
dbo.SCCOMPANY ON dbo.calls.CUSTOMER_NAME IN (dbo.SCCOMPANY.COMPANY, dbo.SCCOMPANY.COMPANY_FULL_NAME)
WHERE (dbo.calls.MENU_TYPE_TO_USE IS NULL OR
dbo.calls.MENU_TYPE_TO_USE <> 'SECURITY MANAGEMENT') AND (dbo.calls.CALL_VIA IS NULL OR
dbo.calls.CALL_VIA <> 'APTI-PSS') AND (dbo.calls.CREATE_SITE_ID IS NULL OR
dbo.calls.CREATE_SITE_ID <> 'GCS-NZT') AND (dbo.calls.CLOSE_TYPE IS NULL OR
dbo.calls.CLOSE_TYPE NOT IN ('Cancel', 'Relogged')) AND (dbo.calls.CALL_STATUS IS NULL OR
dbo.calls.CALL_STATUS <> 'Cancel') AND (dbo.calls.CUSTOMER_NAME NOT IN ('MULTISYS', 'PRIMUS'))
go

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-07 : 21:37:12

Can't include an outer join for one.
Have a look at creating indexed views in bol for the conditions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Phil hehir
Starting Member

2 Posts

Posted - 2006-02-07 : 22:00:00

You are correct, I dropped the table to see what else it might be, and I'm then given the real reason for the failure...

Server: Msg 1957, Level 16, State 1, Line 1
Index on view 'ARS.dbo.UNZ_calls cannot be created because the view requires a conversion involving dates or variants.

Looks like the developers will have to get another way to speed the reports up, and this view isn't going to have an index.

Thanks
Phil.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-07 : 22:12:57

1) return date vales, not nvarchars! If those columns are being sorted or filtered on, no indexes can be used. Let the client do the formating.

2) The outer join condition has an OR -- very bad, very slow, and potentially can cause duplicate rows returned. s/b two separate outer joins to aliases of the same table, one for each condition.

3) make sure all underlying tabled are indexed properly before trying any view indexes

4) as always, consider *normalizing* your schema, which will greatly help performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources