Creating indexed view problem.

• Vladimir Sim

  Ten Centuries

  Points: 1310

  More actions

  June 19, 2007 at 4:34 am

  #117527

  Hi,

  I want to create a indexed view for other view to use as some of my

  data are huge in size.

  As my table structure can't be change to minimize side-effect,

  I add an addition column (sno) with int identity(1,1) to make my index unique.

  I have no problem creating the index but problem with the

  unique clustered index.

  Below are the error that I get from sql server 2000 developer version.

  erver: Msg 1957, Level 16, State 1, Line 1

  Index on view 'TestDB.dbo.test_IV cannot be created because the view requires a conversion involving dates or variants.'

  Please advise why and how to fix it.

  Thank you

  Here is my table structure

  CREATE TABLE [dbo].[test] (

   [sno] [int] IDENTITY (1, 1) NOT NULL ,

   [REF_TYP_ID] [int] NOT NULL ,

   [REF_VAL_ID] [int] NOT NULL ,

   [REF_VAL_NM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

   [REF_VAL_STS] [int] NOT NULL ,

   [CREATE_USER_ID] [int] NOT NULL ,

   [UPD_USER_ID] [int] NULL ,

   [EFF_DT] [datetime] NOT NULL ,

   [EXP_DT] [datetime] NOT NULL

  ) ON [PRIMARY]

  GO

  Here is my code to create the indexed view,

  --- code begin --

  IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON

  IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON

  IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON

  IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON

  IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON

  IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON

  IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF

  go

  if object_id('test_IV') is not null

   drop view test_IV

  go

  CREATE VIEW test_IV WITH SCHEMABINDING AS

   select sno, ref_val_id, ref_val_nm, ref_typ_id

   from dbo.test

   where exp_dt like '%9999%'

   

  go

  create unique clustered index PK_test on test_iv(sno, ref_val_id, ref_typ_id)

  go

   

   

   

• Ken McKelvey

  SSCoach

  Points: 18894

  More actions

  June 19, 2007 at 4:54 am

  #713770

  I am not sure what "where exp_dt like '%9999%'" means but I suspect it is causing the problem.

  If you are looking for dummy dates in the year 9999, try WHERE exp_dt >= '99990101'.

• NAVJOAT-340047

  SSC Enthusiast

  Points: 148

  More actions

  June 20, 2007 at 8:30 pm

  #714236

  maybe try converting the value into a date

  CREATE VIEW test_IV WITH SCHEMABINDING AS

   select sno, ref_val_id, ref_val_nm, ref_typ_id

   from dbo.test

  where exp_dt >= convert(datetime, '9999-01-01', 120)

   

  go

• Vladimir Sim

  Ten Centuries

  Points: 1310

  More actions

  June 20, 2007 at 11:33 pm

  #714249

  Hi,

  Thank everyone for their advise,

  I found out the exp_dt column which is datetime type is causing the error.

  If I change it to convert(char(12),exp_dt, 103), it will be okay since

  there is no conversion from datetime to date.