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.

How to find out datatype of an expression?

Well. You have an expression that produces a result and you want to know the datatype of it. How will you do it?

For example you have the following expression 10+’20’

select 10+'20'

The result is 30

But you do not know if the result is datatype of INT or VARCHAR

You can find out the resultant datatype in two methods

1 Create a table based on expression

select 10+'20' as data into #t

Now see the structure of the column and find the datatype

EXEC TEMPDB..SP_COLUMNS '#t'

The result is

dt1

2 Use SQL_VARIANT_PROPERTY system function

SELECT SQL_VARIANT_PROPERTY(10+'20','basetype') as datatype

The result is

datatype
-----------------
INT

You can also use a variable in place of an expression to find out it’s datatype