sql - 使用BIGINT的TSQL算术溢出

Question

有人可以帮我弄清楚为什么在下面的示例中尝试设置变量@a时会出错吗？

DECLARE @a BIGINT
SET @a = 7*11*13*17*19*23*29*31
/*
ERROR:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
*/

我现在能弄清楚的是，从内部看，SQL开始做数学运算来评估乘法并将临时结果放入INT，然后将其转换为BIGINT。

但是，如果我将 1.0 * 添加到我的数字列表中，则没有错误，因此，我认为这次SQL使用float作为临时结果，然后将其强制转换为BIGINT

DECLARE @b BIGINT
SET @b =   1.0  *  7*11*13*17*19*23*29*31
/*
NO ERROR
*/

坦白说，我看不到代码有什么问题...这是如此简单...

[我正在使用SQL 2008]

[编辑]

感谢Nathan的 link。
这是我所不知道的好信息，但是我仍然不明白为什么会得到错误以及为什么要通过“技巧”来获得像这样的简单脚本。

作为程序员，我应该知道如何处理吗？

或者，这是一个错误，如果是这样，我将认为此问题已解决。

Answer 1

当您进行这样的计算时，单个数字的存储量正好足以容纳该数字，即:numeric(1,0)。看一下这个:

Caution
When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type. For more information about parameterized queries, see Simple Parameterization.

http://msdn.microsoft.com/en-us/library/ms187745.aspx

编辑

这不是SQL Server中的错误。在同一页面上，它指出:

The int data type is the primary integer data type in SQL Server.

和

SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

这是定义的行为。作为程序员，如果您有理由相信数据会溢出数据类型，则需要采取预防措施来避免这种情况。在这种情况下，只需将这些数字之一转换为 BIGINT即可解决此问题。

DECLARE @a BIGINT
SET @a = 7*11*13*17*19*23*29*CONVERT(BIGINT, 31)