Home > SQL Server > SQL SERVER – SET CONCAT_NULL_YIELDS_NULL – SET Statement @SQLServer

SQL SERVER – SET CONCAT_NULL_YIELDS_NULL – SET Statement @SQLServer


SET CONCAT_NULL_YIELDS_NULL : This set statement is used to configure the current environment or session to return NULL value while The NULL is concatenated with other values in an expression. This set setting will be ON by default in the future version of SQL Server and it cannot be set to OFF. So, we have to handle this set statement carefully in the current and future application design.

The best practice is to keep the SET CONCAT_NULL_YIELDS_NULL to ON throughout the data processing.

Usage

SET CONCAT_NULL_YIELDS_NULL ON

or

SET CONCAT_NULL_YIELDS_NULL OFF

Example

1. A simple select statement that concatenates a string with NULL value.

When CONCAT_NULL_YIELDS_NULL is OFF, it returns the string value.pic1When CONCAT_NULL_YIELDS_NULL is ON, it returns NULL value.

pic2I hope that you have understood the use of this SET statement. Happy coding.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: