An interesting setting for NULL but don’t use it for future TIP #109

As we discussed earlier in TIP#103 for NULL in which I shared that we have to take extra care for NULL.

Now in this tip I would like to share one of the interesting setting for NULL. Although it is just for knowledge but don’t use it because it is deprecated in future version and by default you this setting is always on.

Still you are thinking for which setting I am talking about.

So , I am talking about “CONCAT_NULL_YIELDS_NULL”  . Let’s understand it by following example . By Default CONCAT_NULL_YIELDS_NULL is ON which means if anything added to NULL will be null as discussed earlier in our tip #103


Now, see what happens when we do it OFF.


So, We clearly saw in above image  when we set the CONCATE_NULL_YIELDS_NULL property to OFF it dissolved the NULL property.

My take on this property is that we should avoid it don’t try to make it OFF explicitly because if we do this then we explicitly breaking some hidden business rules.

I hope you will like this post.

Enjoy !!!

RJ !!

It is OK its NULL only TIP #103


We always take NULL very lightly. Like if we are designing database then whether it is necessary or not we allow the data field to accept null.We think it will not affect anything.

Actually , at some point this is not always true.

According to Microsoft NULL is an Unknown value. It is not EMPTY & ZERO. It is just unknown.

Now according to my experience NULL is very sensitive and should be handle with care.

While we are designing the database and in a particular table if a field can not be null according to business rule then please don’t allow NULL in that field for data consistency.

For example if we have a StudentEnrollment table and in the StudentEnrollment table  we have reference of StudentId which is primary key of StudentId and that can not be NULL then please don’t make the field null able.

Let me share some more interesting facts

1) Two null value not equal :-  Let me explain it with an example

Suppose I have tblStudentSource table  which has different columns and a course column which have some null values  as shown below


Now see below snap where we are comparing the null value course column. You will find in below snap that there is no result while we have null value in course


2)  Use IS NULL or IS NOT NULL  to handle null able column:-  If a column is null and you want to show all the columns which are null then instead of comparing NULL value with column as shown in above figure use IS NULL as shown in below figure


In similar way if we want result which not have null course then we can write below query


3) Handle NULL carefully with IN clause

Let me explain with an example so we have 2 tables which are tblStudentSource (as shown in above (image 1) and another table which is tblCourse as shown in below figure


Now if you see we have course column in tblStudentSource table which has values similar to tblCourse’s course column and tblStudentSource’s course column contain some NULL values

Now suppose we wrote following statement and expecting that it will return all the course which are in tblCourse


Oh !! it is not returning any course. The reason behind it is NULL is not handled properly. Now to get desire result we have to write following statement


So , be always careful when your column has null values.

I hope the above points might help you somewhere.

Enjoy !!!

Keep learning & Keep Sharing !!



Find first not null value from different columns TIP # 47



Sometimes, it might be possible that we need not null value only from particular columns and if all column have null value then we provide a default value.

Lets understand this by a general and very interesting example

suppose a friend come to your house and you want to give him a treat then you check your first column or we can say first option “Is there any thing to eat ?” if that value is null then you go for second column or we can say second option “ Is there any thing to drink ?”

If that value is also null then you will choose 3rd or default option and ask friend to give you treat.

Isn’t it simple Smile. Just kidding Open-mouthed smile

Let’s understand  now with adventurework’s product table.

Suppose, We want to fetch productId, productname,product number, and any property (either color, class) and if both the column (color, class) are null then need to display “No Property found” in the column value.

so  I wrote following query  to achieve this

COALESCE(Color,class,’No Property found’) As productProperty
FROM [Production].[Product]


so if you observer above figure you will find in the records where color found color value appear and if color value is null and class value found the class value appear and if both color and class value is null then we provide simple value which is “No Property found”

I hope this may help you.

Thanks  & Enjoy

RJ !!