SET CONCAT_NULL_YIELDS_NULL

Question

Post reply

SET CONCAT_NULL_YIELDS_NULL

SD1999

Hall of Fame

Points: 3140
More actions
September 24, 2009 at 2:17 pm

#210043

by default SET CONCAT_NULL_YIELDS_NULL database option is set to False
but... when I'm running the
select 'abc' + null , getting the result as a NULL
since it's False in the db properties window it should give me 'abc'...
why is it returning NULL
so unless I'm specifying SET CONCAT_NULL_YIELDS_NULL off
right before the transaction - only than I get the 'abc'
am I missing something?
if SET CONCAT_NULL_YIELDS_NULL is set to False, why am I getting the NULL in the result?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRocker SSCrazy Points: 2217 More actions · Answer 1

Good question, I see the same, DB properties says setting is False ,

SELECT 'abc' + NULL;

GO

-- gives 'NULL' tho.

OTF SSCertifiable Points: 6104 More actions · Answer 2

I'll assume you are running your code from ssms?

If you execute dbcc useroptions you'll see that ssms overrides the database default connection settings.

concat_null_yields_null is set to true.

SD1999 Hall of Fame Points: 3140 More actions · Answer 3

Thank you Chim.

Here is what I found...I can't believe my eyes....

CONCAT_NULL_YIELDS_NULL

When set to ON, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL. For example, concatenating the character string "This is" and NULL results in the value NULL, rather than the value "This is".

When set to OFF, concatenating a null value with a character string yields the character string as the result; the null value is treated as an empty character string. By default, CONCAT_NULL_YIELDS_NULL is OFF.

SET CONCAT_NULL_YIELDS_NULL must be set to ON when you create or manipulate indexes on computed columns or indexed views.

Connection-level settings (set using the SET statement) override the default database setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to SQL Server. For more information, see SET CONCAT_NULL_YIELDS_NULL.

The status of this option can be determined by examining the IsNullConcat property of the DATABASEPROPERTYEX function.

hugh.hemington SSC Eights! Points: 861 More actions · Answer 4

My use of "SET CONCAT_NULL_YIELDS_NULL OFF" cost me a WHOLE DAY yesterday!

The popular stored procedure for exporting a table using BCP, with a header row doesn't work if the CONCAT_NULL is turned off. (search for "BCP command to export data to excel with column headers")

Microsoft recommends using IsNull() with any column which might hold a null value when concatenating INSTEAD of changing the CONCAT value, because changing the value within a stored procedure causes it to recompile each time it is run.

Select good + IsNull(bad,'') + ugly (although their example used a variable like @lastname)

The function either returns the value of the first argument, or it returns the second argument if the first evaluates to NULL.

Personally, I would have preferred that there be no such thing as NULL -- all columns default to '' because null causes way more problems than it's worth. (but I would also have preferred a BCP utility that does the WHOLE JOB, and a debugger, but that's also not what we got from Microsoft)

Whether liberal use of the IsNull function costs more than recompiling your stored procedure is not certain, but that's what I learned, the very, very hard way.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

hugh.hemington (5/23/2010)
My use of "SET CONCAT_NULL_YIELDS_NULL OFF" cost me a WHOLE DAY yesterday!
The popular stored procedure for exporting a table using BCP, with a header row doesn't work if the CONCAT_NULL is turned off. (search for "BCP command to export data to excel with column headers")
Microsoft recommends using IsNull() with any column which might hold a null value when concatenating INSTEAD of changing the CONCAT value, because changing the value within a stored procedure causes it to recompile each time it is run.
Select good + IsNull(bad,'') + ugly (although their example used a variable like @lastname)
The function either returns the value of the first argument, or it returns the second argument if the first evaluates to NULL.
Personally, I would have preferred that there be no such thing as NULL -- all columns default to '' because null causes way more problems than it's worth. (but I would also have preferred a BCP utility that does the WHOLE JOB, and a debugger, but that's also not what we got from Microsoft)
Whether liberal use of the IsNull function costs more than recompiling your stored procedure is not certain, but that's what I learned, the very, very hard way.

Heh... get used to it because they're taking the ability to change the setting away. It will always be ON at some point in the future. And NULL's are only aggravating when they seem to get in the way. There are many places where I depend on NULL's and the fact that concatenation of any NULL results in a NULL to get the job done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hugh.hemington SSC Eights! Points: 861 More actions · Answer 6

Actually, setting the null concat off was an obsolete fix for me anyway because I have since sanitized my data to eliminate nulls. There is no way on earth I'm going to use IsNull() on columns I "think" might contain nulls! That's a mine field I have no intention of marching through.

It's just as well we lose the ability to turn it off since it triggers re-compile on changes and bombs certain procedures when turned off.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

hugh.hemington (5/23/2010)
Actually, setting the null concat off was an obsolete fix for me anyway because I have since sanitized my data to eliminate nulls. There is no way on earth I'm going to use IsNull() on columns I "think" might contain nulls! That's a mine field I have no intention of marching through.
It's just as well we lose the ability to turn it off since it triggers re-compile on changes and bombs certain procedures when turned off.

What did you replace the NULLs with? Empty strings? Could be that all you've done is paint the mines a different color. 😉

Anyway, thanks for the feedback.. I hope it all worked out well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)