How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON 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 A and B, 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 A and B.

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?
Writing a parser to change specific bits of your queries would indeed require some effort. Adding just a single line to every one of them, on the other hand, seems a relatively easy task. So, you could mass-update all existing SQL queries/batches in your application with this line added to the beginning of each batch:

SET CONCAT_NULL_YIELDS_NULL OFF;


and re-configure the DB layer to issue SET CONCAT_NULL_YIELDS_NULL ON; (or just omit it if ON is the default) on connection from then on.

That would allow you to review/modify each existing query in your own time while forcing all new queries, if any, to comply with the new behaviour.

Each existing query, once reviewed, would be stripped off the added SET statement. You could review the queries in groups based on the objects they work with, so that new features could be rolled out on those objects sooner (because all the relevant queries would then be executing in the context of CONCAT_NULL_YIELDS_NULL = ON).