Search code examples
sqlsql-servert-sqlsql-server-2017nullif

T-SQL NULLIF returns NULL for zero


Why the script below returns NULL instead of 0?

DECLARE @number BIGINT = 0;

SELECT  NULLIF(@number, '');

According to the MSDN, it should return 0:

NULLIF
Returns a null value if the two specified expressions are equal.

For SQL server, 0 and '' is considered the same (=equal)? What is the logic behind?


Solution

  • When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

    SELECT CONVERT(bigint, '')
    SELECT CONVERT(float, '')
    SELECT CONVERT(date, '')
    
    0
    0
    1900-01-01
    

    https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql