NULLIF Function in SQL

NULLIF Function

This SQL Server tutorial explains how to use the NULLIF function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the NULLIF function compares expression1 and expression2. If expression1 and expression2 are equal, the NULLIF function returns NULL. Otherwise, it returns the first expression which is expression1.

Syntax

The syntax for the NULLIF function in SQL Server (Transact-SQL) is:

NULLIF( expression1, expression2 )

Parameters or Arguments

expression1, expression2

The expressions that will be compared. Values must be of the same datatype.

What is the Default Datatype of NULL?

What is the Default Datatype of NULL?

NULL is undefined and every column can have NULL values except columns with timestamp datatype (which represent NULL values differently). There is a simple method to know the default datatype of NULL values.

First, let us create a table with SELECT INTO TABLE Syntax and then we will try to find out the datatype of the column where we have stored NULL. Please note that we are not providing any datatype for the NULL value.

SELECT NULL AS col

INTO #TempTable

Now let us look at the structure of the table and see what datatype is assigned to the column named col in the table #TempTable

EXEC tempdb..sp_columns #TempTable

Now please look at the column named Type_Name in the resultset. You will find that the default datatype is an Integer.

So, theoretically, I agree that it is difficult to come up with the datatype of the NULL value, but in SQL Server the default datatype of the NULL is an Integer.