views:

21

answers:

0

Here's a table with a computed column in SQL Server 2005:

CREATE TABLE footable AS
  [identifier] nvarchar(255) NOT NULL,
  [result]     AS CASE 
    WHEN [identifier] like '%\[%]' escape '\'
    THEN LEFT( [identifier], CHARINDEX('[',[identifier]) - 1 )
    END

Here's what I'm expecting:

identifier      result
======================
foo[bar]        foo
foo             NULL

This worked a few days ago, and has been working for over a year.

Then yesterday, I started getting this error when inserting or updating with identifier values that had no [, when a NULL would be the expected result:

Invalid length parameter passed to the left function.

Now, today, it's working again.

Why would SQL Server be attempting to solve the LEFT() function when the CASE is not true?

More importantly, why is it working one day and not the next?

I was also creating some indexed views yesterday, though not covering either of these columns. Could one of the SET options required for indexing a view cause the database to start throwing errors for an expression like this?