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?