NULL – Typed and Non-Typed

We know that NULL denotes the absence of value or NULL is known as undefined. Direct use of NULL in some cases may work or throw an error.

Consider the following example 1

SELECT SUM(NULL) as No

It throws an error

Msg 8117, Level 16, State 1, Line 10
Operand data type NULL is invalid for sum operator.

Consider another example 2

declare @no int
set @no=null 
SELECT SUM(@no) as No

The result is

No
-----------
NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

Do you know why the first example failed whereas second example worked well? Because in example 1 the NULL does not have any datatype and SUM function does not recognize it and throws an error. In second example NULL is assigned to a variable of type INT and SUM function worked well.

In first example, the NULL is Non-Typed and in the second example the NULL is Typed (INT datatype)

You can also do implicit conversion to make Non-Typed NULL into Typed NULL as shown below

SELECT SUM(NULL+1) as No

The result is

No
-----------
NULL
Warning: Null value is eliminated by an aggregate or other SET operation.
Advertisements

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