MSSQL Server : NULL – Typed and Non-Typed

 

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

1	SELECT SUM(NULL) as No

It throws an error

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

Consider another example 2

1 2 3

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

The result is

1 2 3 4

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

1	SELECT SUM(NULL+1) as No

The result is

1 2 3 4

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