Finally, none of our customers are using SQL Server 2005 anymore, so we can drop support for it and start using all those awesome "new" SQL Server 2008 features (indexed views, filtered indexes, query notifications, ...) in our applications.
Or so we thought.
Unfortunately, those new features require
CONCAT_NULL_YIELDS_NULL to be set to
ON---not only when using the new features but basically whenever the database is accessed: If I create an indexed view
V on tables
CONCAT_NULL_YIELDS_NULL must be set to
ON not only when working with (new) view
V, but also when inserting/updating values in (old) tables
Now we have a huge legacy application with a lots of SQL statements scattered all over the code: A quick search for
SELECT over the code base yields ~10.000 results. The DB layer executes
SET CONCAT_NULL_YIELDS_NULL OFF after opening the DB connection, so all SQL statements were written under the assumption that
A + NULL = A rather than
A + NULL = NULL.
Is there any "slow upgrade path" to move from
CONCAT_NULL_YIELDS_NULL OFF to
ON without a one-time-effort to check, rewrite and test Every. Single. SQL statement?
I was thinking about parsing "old" SQL statements in the DB layer and replacing
expression1 + expression2 with
ISNULL(expression1, '') + ISNULL(expression2, ''), but for that I'd need to (1) implement a parser for the subset of T-SQL that we currently use and (2) determine the result type of T-SQL expressions, so that I know whether
+ means string concatenation or addition. Both should be doable but sound like a huge amount of non-trivial work.
Is there any other option that I've missed? Or did, by any chance, someone already implement a converter from non-CONCAT_NULL_YIELDS_NULL-ON-compatible SQL statements to CONCAT_NULL_YIELDS_NULL-ON-compatible SQL statements?