• Home
  • DSA
  • Concept
  • Interview
  • Tips&Tricks
  • Tutorial Videos
  • Home
  • DSA
  • Concept
  • Interview
  • Tips&Tricks
  • Tutorial Videos
  • #News
  • #APPS
  • #Events
    • #WWDC
    • #I/O
    • #Ignite
  • #Let’s Talk
  • #Advertise

MyCodeTips mycodetips-newlogocopy1

  • Home
  • DSA
  • Concept
  • Interview
  • Tips&Tricks
  • Tutorial Videos
Programming

Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT in SQL Server 2012 onwards

CONCAT_NULL_YIELDS_NULL will be always ON in the future so avoid setting it off. What this function essentially does is that when it is on it will return a null value when any other value is connected it with using + operator. If CONCAT_NULL_YIELDS_NULL is set to OFF it will return the original value instead of NULL.

Let us see a simple example of the same.

-- Before SQL Server 2012
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO

f4269664d5 CONCAT NULL YIELDS NULL1

However, in SQL Server 2012 the behavior of the ‘+’ operator remains the same but the behavior of function CONCAT is bit different and it totally ignores this value. Here is the example of it.

--- SQL Server 2012 and onwards
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO

f4269664d5 CONCAT NULL YIELDS NULL2

I assume this may be intentional as in future the value of CONCAT_NULL_YIELDS_NULL will be always ON. Irespectively I find it very interesting how CONCAT function works with NULLs.

 

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT ‘abc’ + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT ‘abc’ + NULL yields abc.

If SET CONCAT_NULL_YIELDS_NULL is not specified, the setting of the CONCAT_NULL_YIELDS_NULL database option applies.

SET CONCAT_NULL_YIELDS_NULL must be ON when creating or altering indexed views, indexes on computed columns, filtered indexes or spatial indexes. If SET CONCAT_NULL_YIELDS_NULL is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns, filtered indexes, spatial indexes or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see “Considerations When You Use the SET Statements”

Liked it? Take a second to support Ranjan on Patreon!
become a patron button
  • Click to share on Reddit (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • More
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
Written by Ranjan - 1248 Views
Tags | SQL
AUTHOR
Ranjan

Namaste, My name is Ranjan, I am a graduate from NIT Rourkela. This website is basically about of what i learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures, its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

mycodetips-newlogo2

Tips to protect your site from hackers

November 8, 2013
mycodetips-newlogo2

RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds

May 25, 2013
sqlserver-fetchnext

OFFSET and FETCH NEXT ROWS in SQL Server 2012?

June 6, 2013
Next Post
Previous Post

Support us

mycodetips mycodetips
Follow us @ LinkedIn 2850+

Subscribe for updates

Join 8,213 other subscribers

Latest Posts

  • YT-Featured-solidprinciples
    SOLID Principles of Software Design
  • IOS 16 Features
    Latest features in IOS 16
  • r-language
    How can R language be used for data analysis?
  • wordpress-coding-blog
    Guide To WordPress Coding Standards
  • YT-Featured-Algorithm
    What is Algorithm?
  • Frameworks of IOS
    Frameworks of IOS – Part ( I )
  • NSFileManager or NSPathUtilities
    NSFileManager or NSPathUtilities in Objective-C
  • Passing data between view controllers in Objective-C
    Passing data between view controllers in Objective-C
  • structures-classes-enum
    Structures and Classes in swift !
  • control-system-swift
    Control Flow in Swift
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

techlynk

Techlynk

techbyte

Do2Day

techbyte

Techbyte

  • #about
  • #myapps
  • #contact
  • #privacy
  • #Advertise
  • #Guestpost
  • #myQuestions

Android Android Studio API APP Programming Apps blogging CSS DATABASE dsa Features HTML HTML5 installation Interview Questions IOS iPhone javascript Mac objective-c OS Programming quicktips SDK SEO SQL swift Tips & Tricks Tools UI Web Wordpress Xcode

  • SOLID Principles of Software Design
  • Latest features in IOS 16
  • How can R language be used for data analysis?
  • Guide To WordPress Coding Standards
  • What is Algorithm?

©mycodetips.com