2

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?

2
  • Is the SET command executed by the code or internally by the client API? Perhaps upgrading provider/driver to a modern one take care of the set options. Of course, there may be behavior changes that require regression testing but that should be done anyway when upgrading the SQL version. – Dan Guzman Jul 21 '16 at 11:44
  • @DanGuzman: The regressions are exactly the point: Sure I could just change SET CONCAT_NULL_YIELDS_NULL OFF to SET CONCAT_NULL_YIELDS_NULL ON in the DB layer, but I cannot regression test 10.000 legacy SQL statements at once (since it's legacy code, it doesn't have unit tests either). Thus, I need some kind of compatibility layer to keep those working until, over time, they can be tested and flagged as "OK with CONCAT_NULL_YIELDS_NULL ON". – Heinzi Jul 21 '16 at 11:49
1

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).

3
  • Nice idea, but that won't work: As stated in the question: "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." Using the new features will break existing code if the existing code uses SET CONCAT_NULL_YIELDS_NULL OFF;. – Heinzi Jul 21 '16 at 19:10
  • Yes, I understand that. I tried to cover that by suggesting you review your queries in groups based on the objects they work with. Say, you want to create an indexed view on A and B and out of the 10,000+ queries there are, say, 1000 currently working with those tables. So you review those queries first. When all of them have been reviewed (and the SET line removed), you will be able to add the view, because all the relevant queries will now be running under CONCAT_NULL_YIELDS_NULL = ON. And you can continue reviewing other queries. – Andriy M Jul 21 '16 at 19:16
  • Good point, that would reduce the review burden a bit. – Heinzi Jul 21 '16 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.