Indexed views are a powerful feature in SQL Server that can greatly improve performance by materializing views into hard data. However, there are certain restrictions and bugs that can make working with indexed views challenging. In this article, we will explore one such bug related to using indexed views with calculated columns.

Let’s start by creating a table and a view on it:

CREATE TABLE myTable (
    Id INTEGER NOT NULL,
    InView CHAR(1) NOT NULL,
    SomeData VARCHAR(255) NOT NULL
)

CREATE VIEW vwIxView WITH SCHEMABINDING AS
SELECT 
    ID,
    SomeData,
    LEFT(SomeData, CHARINDEX('x', SomeData)-1) AS leftfromx
FROM dbo.myTable
WHERE InView = 'Y'

In this example, the view filters the data based on the condition “InView = ‘Y'” and adds a calculated column called “leftfromx” that extracts characters up to and including the first ‘x’ from the “SomeData” column.

If we insert some data into the view:

INSERT INTO myTable (Id, InView, SomeData)
SELECT 1, 'N', 'a'

As expected, if we query the view, there will be no data in it.

Now, let’s add an index to the view:

CREATE UNIQUE CLUSTERED INDEX pkvwIxView ON vwIxView(Id)

The data is now persisted. Let’s add some more data, but this time in a 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

Now, if we query the view, we encounter an error related to the “LEFT” function. But why is this happening when the condition “InView = ‘N'” should prevent the execution of that piece of code?

By examining the estimated execution plan, we can better understand the flow of events. The “compute scalar” operation, where the “LEFT” function is executed, happens before the filter. Since there is no ‘x’ in the “SomeData” column, it is not surprising that the function throws an error.

This bug has been tested on both SQL Server 2008 R2 and 2012 RC0. If you have encountered a similar issue, you can upvote the corresponding Connect item here.

Indexed views can be a valuable tool for improving performance in SQL Server, but it’s important to be aware of potential bugs and limitations. By understanding how calculated columns and indexed views interact, you can avoid unexpected errors and ensure the smooth operation of your database.