Is CONCAT_NULL_YIELDS_NULL SQL a persistent runtime setting? (Server 2000 and up)

Question

Ok, i know CONCAT_NULL_YIELDS_NULL will always be set to ON in future versions of SQL (insert MSDN search params(yadda,yadda)) so bear with me.

Boring details: The platform is MSSQL 2000 Enterprise (v8 sp4) AKA Critatious Period Edition.

The following will evaluate to NULL

SELECT 'abc' + NULL; 

Understandable. But you can circumvent this with the following:

SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'abc' + NULL; 

In which case the result is "abc". From here on, future sql statements will allow concatenation with NULL. But is this a 'persistent' runtime setting? Such as, is this setting only applied for my session to the SQL server, or does it apply to statements executed by all users?

As far as i can tell, after setting _YIELDS_NULL to ON, a restart to the MSSQL services will have it default back to OFF (correct me if i'm wrong).

Last thing: I'm not actually planning to put this into practice. A third-party stored procedure failed (looks like they might have updated it, breaking it). Best i can figure is that they implemented it with the assumption that "SET CONCAT_NULL_YIELDS_NULL" was set to ON. And it used to always work.

I'm just looking for a cause: Is there a way to have CONCAT_NULL_YIELDS_NULL set to ON upon SQL server startup?

Answer 1

As BOL goes,

If a SET statement is set in a stored procedure, the value of the SET option is restored after control is returned from the stored procedure.

Therefore, the developer has very little to do to ensure correct settings. They could just set them in the beginning of the SP and didn't even have to restore original values afterwards.

Answer 2

CONCAT_NULL_YIELDS_NULL can be set:

• per connection - when you close connection and open new, it's back to default

• per database

  • open Microsoft SQL Server Management Studio, rightclick your database and select properties. It's in the Miscellaneous section.

• or from T-SQL

  ALTER DATABASE database_name SET { CONCAT_NULL_YIELDS_NULL OFF }

Note: some clients can issue set concat_null_yields_null on command when opening connection. For example SQL Server Management Studio connecting to SQL 2005 does. Use SQL Server Profiller to find about your connection.

I do not have SQL 2000 on my notebook to test exactly on that version.