CONCAT_NULL_YIELDS_NULL Can be used to control the Concatenation results of stings with NULLS.
When turned on:
Concatenation of string with NULL Results in NULL.
When turned Off:
Concatenation of string with NULL Results in String itself.
Sample Queries:
[sql]
SET CONCAT_NULL_YIELDS_NULL ON
SELECT ‘TestString’ + NULL AS Result
–Results in NULL
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT ‘TestString’ + NULL AS Result
–Results in TestString
[/sql]
The first result would be NULL and the Second result would be TestString.
In the above query the Impact of changing the property value can be clearly seen.
CONCAT_NULL_YIELDS_NULL setting can be changed in Session or in Database.
Setting CONCAT_NULL_YIELDS_NULL value in Session:
[sql]
SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL OFF
[/sql]
If this setting is changed in the Session then the impact of changing this value can be seen in the current session only. All the other session will have its default behaviour.
Setting CONCAT_NULL_YIELDS_NULL value for a Database:
[sql]
ALTER DATABASE <<databasename>> SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE <<databasename>> SET CONCAT_NULL_YIELDS_NULL ON
[/sql]
If the value of CONCAT_NULL_YIELDS_NULL is not set in the query then the database property for CONCAT_NULL_YIELDS_NULL is used By default .
How to check the current setting of CONCAT_NULL_YIELDS_NULL?
The Below query fetches the session value for the CONCAT_NULL_YIELDS_NULL property.
[sql]
SELECT SESSIONPROPERTY(‘CONCAT_NULL_YIELDS_NULL’)
–Result 1 if set to ON, 0 if Set to OFF
[/sql]
The Below query fetches the database value for the CONCAT_NULL_YIELDS_NULL property.
[sql]
SELECT NAME,IS_CONCAT_NULL_YIELDS_NULL_ON FROM SYS.DATABASES
–Result 1 if set to ON, 0 if Set to OFF
[/sql]
Point to Note:
– Some clients Like SQL Server Management studio by default send the CONCAT_NULL_YIELDS_NULL ON command while initiating each database connection.
Hence even after changing the database property the result cannot be seen in SSMS.
– Setting this value in stored procedures may cause the procedure to recompile every time its executed.
– This setting in future versions of SQL Server is going to be Always ON, So be cautious while using this.
http://support.microsoft.com/kb/294942
http://msdn.microsoft.com/en-us/library/ms176056.aspx