On running this query, I am getting error, Any Idea why?
select ISNULL(NULLIF(0,0), -1)
Error :
Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = -1.
EDIT -- another example:
select ISNULL(NULLIF(0.0,0.0), 1.0)
Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric.
This work:
select ISNULL(NULLIF(cast(0 as int),0), -1)
SQL optimalizer do "hidden" cast to smallest data type.
From documentation of NULLIF (http://technet.microsoft.com/pl-pl/library/ms177562%28v=sql.110%29.aspx):
Returns the same type as the first expression.
So NULLIF returns tinyint and ISNULL try to replace tinyint to -1 and then you have overflow
When you cast first parameter (0) to int (or smallint) NULLIF returns your "new" data type which is right for -1
To find the actual type being used:
SELECT NULLIF(0,0) test_col INTO #test_table
SELECT data_type, numeric_precision, numeric_scale
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '#test_table%' AND COLUMN_NAME = 'test_col'
This is strange behaviour anyway. Why are constants like "0" evaluated different ways - sometimes as int and sometimes as tinyint. If I do SELECT selecting a constant "0" then it is evaluated as "int". But passing "0" to NULLIF() the constant is evaluated as tinyint. And there is no mention of this behaviour in the NULLIF documentation at all.
-- 1) SELECT evaluates numeric constant "0" as "int":
DROP TABLE IF EXISTS #t
GO
SELECT c = 0
INTO #t
GO
EXEC tempdb..sp_help #t
GO
-- 2) NULLIF() evaluates numeric constant "0" as "tynyint":
DROP TABLE IF EXISTS #t
GO
SELECT c = NULLIF(0, 0)
INTO #t
GO
EXEC tempdb..sp_help #t
GO
