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.

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

createuniqueclusteredindex 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.

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.

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.