Search code examples
sql-servert-sqlsqlclient

How can I observe the effect of SET CONCAT_NULL_YIELDS_NULL off?

We have an old database product; the master deployment has always done ALTER Database [databasename] SET CONCAT_NULL_YIELDS_NULL OFF. Since this setting is going away, we want to come up with a testing plan for getting rid of it and tracking down what code depends on it.

With this switch off, the documentation says that SELECT 'abc' + NULL FROM sometable results in abc rather than NULL.

I can easily observe this behavior at the connection level; however the application never sets it at the connection level. That's not the question here.

How can I observe by any SQL statement the effect of turning off CONCAT_NULL_YIELDS_NULL only at the database level using .NET System.Data.SqlClient as the database access client?

We audited the entire codebase and removed all instances of SET commands except for transaction isolation level as these hose things pretty hard. While I tried EXEC to see if it would bypass the driver settings and found it didn't, I'm reasonably certain there's some sufficiently exotic SQL construction that could succeed.

Solution

  • I think I have finally understood what you actually want: to change the connection option without changing it. No, there are no ways to achieve this, otherwise I would have heard about them, and Rutzky would have mentioned them in his answer I referenced in my original response.

    There is a strict hierarchy in a way the options can be set:

    1. Instance-wide settings for all databases, using sp_configure 'user options'. Lowest level defaults.
    2. Database-specific options which can be set via ALTER DATABASE. Take precedence over instance defaults.
    3. Explicit SET statements executed on the connection. Override everything.

    The advice? Change the application' connection string, if you can, so that it would use another driver that doesn't set / touch this option. Assuming, of course, it wouldn't crash anything, but this is a thing which can be tested relatively quickly.

    Also, you may try to change the instance defaults, and see if the driver somehow favours them over #2. It is possible that it can be too smart for its own good.

    P.S. For the sake of completeness, the only cases of interdependent options that I know of are listed below:

    1. Shortcut settings, for example ANSI_DEFAULTS. Setting it actually affects several options related to ANSI compatibility;
    2. SET LANGUAGE. When you change the connection language, it also overrides the DATEFIRST and DATEFORMAT options.

    P.P.S. I decided to leave my previous answer intact, as it still contains some info which might be useful for people who would understand your question the way I initially did.