sql-server-2008-r2 - SQL Server 2008 R2 算术溢出错误将数字转换为数据类型数字

Question

我有一个令人困惑的错误，我在 SQL Server 2008 R2 上无法理解。

但是当我在本地服务器(也有 SQL Server 2008 R2)上尝试相同的请求时，一切正常。

所以这是提出问题的请求:

select cast(cast(1.260 as numeric(13,3)) as numeric(10,2))

我还添加了一些查询的结果，指示每个服务器的环境:

在本地服务器上:

---------------------------------------
1.26

(1 row(s) affected)

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) 
    Apr 22 2011 19:23:43 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

ARITHABORT
---------------------------------------------------------------------------------------------
1

(1 row(s) affected)

ARITHIGNORE
---------------------------------------------------------------------------------------------
NULL

(1 row(s) affected)

ANSI_WARNINGS
---------------------------------------------------------------------------------------------
1

(1 row(s) affected)

在远程服务器上:

Msg 8115, Level 16, State 7, Line 1
Arithmetic overflow error converting numeric to data type numeric.

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
    Jun 17 2011 00:54:03 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)
(1 row(s) affected)

ARITHABORT
------------------------------------------------------------------------------------------------------------
1

(1 row(s) affected)

ARITHIGNORE
------------------------------------------------------------------------------------------------------------
NULL

(1 row(s) affected)

ANSI_WARNINGS
------------------------------------------------------------------------------------------------------------
1

(1 row(s) affected)

我的问题是如何重现远程服务器上发生的问题。如您所见，参数 ARITH... 和 ANSI_.. 在两台服务器上是相同的。 SQL Server 上是否有关于此类错误的任何配置？

Answer 1

NUMERIC_ROUNDABORT选项开启

When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision occurs in an expression. When OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

通常这是关闭的，因为打开索引 View 等可能会失败。

我从来没有改变过，从来没有。

SET NOCOUNT ON;
GO
PRINT 'ON'
set NUMERIC_ROUNDABORT ON;
select cast(cast(1.260 as numeric(13,3)) as numeric(10,2));
GO
PRINT 'OFF'
set NUMERIC_ROUNDABORT OFF;
select cast(cast(1.260 as numeric(13,3)) as numeric(10,2));
GO

给

ON

---------------------------------------
Msg 8115, Level 16, State 7, Line 3
Arithmetic overflow error converting numeric to data type numeric.

OFF

---------------------------------------
1.26