CONCAT_NULL_YIELDS_NULL Default to OFF

Question

Post reply

CONCAT_NULL_YIELDS_NULL Default to OFF

AVB

SSChampion

Points: 11418
More actions
May 9, 2008 at 1:36 pm

#71261

Is there somewhere to set the default setting to CONCAT_NULL_YIELDS_NULL ?
I use it pretty often but just found out it causes a recompile when I change it to OFF.
I'd like to set it to OFF as the default if possible.
I know I can use ISNULL but that would be more work!

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

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

AVB SSChampion Points: 11418 More actions · Answer 1

Okay I found were you can set your default options in the Database Property:

Syntax

DATABASEPROPERTY( database , property )

IsNullConcat Null concatenation operand yields NULL. 1 = TRUE

0 = FALSE

NULL = Invalid input

Any reason why I shouldn't do this?

BOL said "SET CONCAT_NULL_YIELDS_NULL must be set to ON when you create or manipulate indexes on computed columns or indexed views". Aside from that any other reasons?

AVB SSChampion Points: 11418 More actions · Answer 2

Okay now I'm really confused...

I set the DBOptions in a Test DB to Concat Null Yields Null to True and False and with both settings I still get a null concatanation.

declare @Char varchar(20),@Null varchar(20),@Body varchar(500)

SET @Char = 'Test!'

SET @NULL = null

SET @Body = @Char + ' ' + @NULL

PRINT @Body

Any suggestions?

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 3

This option is probably over written by your client.

If you are using query analyzer, you can go to Options.Connection Properties. There is a checkbox there.

I'd recommend putting the set option in your code, however. Relying on the default properties of the connection to set a non-standard option will likely cause portability issues in the future.

Kyle

AVB SSChampion Points: 11418 More actions · Answer 4

Thanks for the reply. I guess I'm going to have to isnull a whole lot of stuff! ughh!

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 5

Not necessarily -- depending on your querying application...

To use your previous example, add the SET to the top of the statement. It'll work the same way every time, regardless of DB or connection settings.

SET CONCAT_NULL_YIELDS_NULL OFF

declare @Char varchar(20),@Null varchar(20),@Body varchar(500)

SET @Char = 'Test!'

SET @NULL = null

SET @Body = @Char + ' ' + @NULL

PRINT @Body

AVB SSChampion Points: 11418 More actions · Answer 6

But will it force a recompile doing it that way?

or does the recompile happen cause the setting is changing from the default setting?

AVB SSChampion Points: 11418 More actions · Answer 7

I set the options in Managment Studio to "off". So I don't have to use the SET Command within the Procs. It didn't recompile so I guess that works.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Just a recommendation... Like you said, use ISNULL... don't mess with the default server settings on this...

--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)