CONCAT_NULL_YIELDS_NULL

Posted on : 30-03-2012 | By : Devi Prasad | In : Configuring SQL Server, SQL Server Basics

Share:

0


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:

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

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:

SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL OFF

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:

ALTER DATABASE <<databasename>> SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE <<databasename>> SET CONCAT_NULL_YIELDS_NULL ON

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.

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')
--Result 1 if set to ON, 0 if Set to OFF

The Below query fetches the database value for the CONCAT_NULL_YIELDS_NULL property.

SELECT NAME,IS_CONCAT_NULL_YIELDS_NULL_ON FROM SYS.DATABASES
--Result 1 if set to ON, 0 if Set to OFF

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

(Visited 49 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , , , , , , , , , , , , , , , , , , , , , ,