Arithmetic overflow error for data type tinyint, value = -1

Question

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.

Answer 1

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'