数据类型为tinyint的算术溢出错误，值= -1

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

Msg 220,Level 16,State 2,Line 1

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

数据类型为tinyint的算术溢出错误,值= -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.

消息8115,级别16,状态8,行1算术溢出错误将数字转换为数据类型数字。

1 个解决方案

#1

This work:

select ISNULL(NULLIF(cast(0 as int),0), -1)

SQL optimalizer do "hidden" cast to smallest data type.

SQL优化器对最小数据类型执行“隐藏”转换。

From documentation of NULLIF (http://technet.microsoft.com/pl-pl/library/ms177562%28v=sql.110%29.aspx):

从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

所以NULLIF返回tinyint,ISNULL尝试将tinyint替换为-1然后你有溢出

When you cast first parameter (0) to int (or smallint) NULLIF returns your "new" data type which is right for -1

当您将第一个参数(0)强制转换为int(或smallint)时,NULLIF将返回适合-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'

#1