SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

[BUG] Inserts to tables with an index view can fail

Unfortunately some of the more troubling bugs can be very hard to reproduce succinctly.  Here is one that has been troubling me for a little while :

The issue is using indexed views with a calculated column. Indexed views, despite their restrictions, are a very handy addition to SQL Server and materializing views to be hard data can certainly improve performance.  So to demonstrate my issue we will need to build a table and create a view on it. 

create table myTable
(
Id integer not null,
InView char(1) not null,
SomeData varchar(255) not null
)
go
Create view vwIxView
with schemabinding
as
Select ID,Somedata,left(SomeData,CHARINDEX('x',SomeData)-1) as leftfromx
from dbo.myTable
Where InView ='Y'
 
As you can see , the view is filtering the data for where InView =’Y’ and is adding a calculated column to do some manipulation of the column ‘SomeData’. This column ,leftfromx, is taking the characters up to and including the first ‘x’ from the ‘SomeData’ column.

If we insert some data into the view with

insert into myTable(Id,InView,SomeData)
select 1,'N','a'

unsurprisingly, if we look to the view then there will be no data in it.

Now lets add an index to the view

create unique clustered index pkvwIxView on vwIxView(Id)

The data is now persisted.

Lets now add some more data ,the same data, in a ever so slightly different way.

declare @id integer,
@inview char(1),
@Somedata char(50)
select @id = 1, @inview = 'N',@Somedata = 'a'

insert into myTable(Id,InView,SomeData)
select @id,@inview,@Somedata

What is the result ?

image

Huh , well its kind of obvious which “LEFT or SUBSTRING function” has errored, but as inview = ‘N’ why should that piece of code even been executed ?  Looking at the estimated plan we can more easily see the flow of events.

image

The ‘compute scalar’ operation is where the LEFT is being executed. ,That is happening before the filter and as there is no ‘x’ in the ‘SomeData’ column , it is unsurprising that the function is erroring.  I have tested this on both 2008r2 and 2012 rc0.

I have raised a connect item here, if you want to upvote it.

Comments

Peso said:

It's not really a bug, it's a feature and it's documented in Books Online for SQL Server 2005.

See "Expressions in queries" here technet.microsoft.com/.../ms143359(SQL.90).aspx

See this example, which is the same thing.

SELECT Column_Name

from    (

              SELECT Column_Name

               FROM    (

                               select 'staff' as Column_Name union all

                               select '234000' as Column_Name

                       ) as Table_Name

               WHERE   ISNUMERIC(Column_Name) = 1

       ) as d

where    CAST(Column_Name AS INT) <= 1000000

# January 7, 2012 8:01 AM

Dave Ballantyne said:

Hi Peter,

 Hmm , thats a pretty tenuous piece of documentation :)  

From a user perspective there should be no difference between using literal values and variables that is why im calling it a bug.

Indeed, even if you :

insert into myTable(Id,InView,SomeData)

select 1,'N','a'

union all

select 1,'N','a'

the problem also occurs.

Im not seeing it as the same as your example , as there is a definate path of logic here that should be obeyed.  The calculation is only useful and can only be used if InView = 'Y'.  Though i doubt that this will be something simple to solve by the team.

This also does make me think of the inefficiences here too,  the operations are firing when the output will not be used.  Maybe thats also something worth exploring later.

Dave

# January 7, 2012 12:18 PM