June 19, 2007 at 4:34 am
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
June 19, 2007 at 4:54 am
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'.
June 20, 2007 at 8:30 pm
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
June 20, 2007 at 11:33 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply