Lesson Learned #76: The strange case between an indexed view and date type conversion

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

Hello Team,

Today, it was my second case that I have found a thing that we need to remember with the definition of an indexed views. So, this is the history:

Our customer has the following table and indexed view definition:

CREATE TABLE [dbo].[Test](
	[ID] [int] NOT NULL,
	[Date_To_Text] [nvarchar](200) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE View TestVm3
WITH SCHEMABINDING
AS 
SELECT ID, CONVERT(date,Date_to_text,102) as X FROM DBO.Test

create unique clustered index TestVm3_i on testvm3 (id)

Until here, any problem and everything is working fine but, we could start adding new rows in the table:

INSERT INTO TEST (ID) VALUES(1)
INSERT INTO TEST (ID,date_to_text) VALUES(2,Null)
INSERT INTO TEST (ID,date_to_text) VALUES(3,'2019.01.01')
INSERT INTO TEST (ID,date_to_text) VALUES(4,'2019.01.02')

if we run the view using with or without (noexpand) the query works as expected returning all the rows:

select * from TestVm3

But, what will happen if I insert a value like this TSQL:

INSERT INTO TEST (ID,date_to_text) VALUES(5,'NOTGOODVALUE')

In this case, I'm going to have an error message like this one: Msg 241, Level 16, State 1, Line 20 - Conversion failed when converting date and/or time from character string.

Why? because, every time that you update a row in the table that has a dependency with indexed view, this indexed view will be updated and as the conversion is not valid you're going to have this error.

What is the workaround?, if there is needed to have this conversion, use, for example try_convert like this one:

ALTER View TestVm3
WITH SCHEMABINDING
AS 
SELECT ID, try_CONVERT(date,Date_to_text,102) as X FROM DBO.Test
create unique clustered index TestVm3_i on testvm3 (id)

Just a comment here, if you run the ALTER view of an Indexed View the Clustered Index will be removed, for this reason, you need to create it again.

My second lesson learned here: if you don't know if your table has an indexed views, review its dependencies and you could find out them.

Enjoy!

Leave a Reply Cancel reply