SQL Server 错误或功能?小数转换

在开发过程中遇到了相当奇怪的 SQL Server 行为.在这里，对于完全相同的数字，我们有完全相同的公式.唯一的区别是我们如何获得这个数字(4.250).来自表、临时表、变量表或硬编码值.舍入和铸造在所有情况下都是完全一样的.

-- 普通表创建表 [dbo].[值]([val] [十进制] (5, 3) 非空)插入 [值] 值 (4.250)SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr-- 来自普通表的内联查询SELECT * FROM (SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr)-- 无表记录SELECT ROUND(CAST(4.250 * 0.01/12 AS DECIMAL(15, 9)), 9) AS val-- 表变量将 @value 声明为表 (val [十进制] (5, 3));插入@value 值 (4.250)SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val FROM @value-- 临时表创建表#value(val [十进制] (5, 3))插入 #value 值 (4.250 )SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr-- 所有记录放在一起SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr联合所有SELECT ROUND(CAST(4.250 * 0.01/12 AS DECIMAL(15, 9)), 9) AS val联合所有SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val FROM @value联合所有SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr删除表#value;删除表 [dbo].[值]；

结果是:

解决方案

这似乎是因为你没有指定 4.250 的数据类型，你已经硬编码了这个值，以及混合数据类型 decimal(5,3) 和 decimal(15,9) 在表声明和强制转换语句中.

请注意，在任何地方都指定相同的精度:

-- 普通表创建表 [dbo].[值]([val] DECIMAL(15, 9) 非空)插入 [值]选择 CAST(4.250 作为十进制(15、9))SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS valFROM [值] AS pr-- 来自普通表的内联查询选择 *FROM (SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS valFROM [值] AS pr) a-- 无表记录SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01/12 AS DECIMAL(15, 9)), 9) AS val-- 表变量将@value 声明为表(val [十进制] (15, 9));插入@值选择 CAST(4.250 作为十进制(15、9))SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val来自@值-- 临时表创建表#value(val [十进制] (15, 9))插入#value选择 CAST(4.250 作为十进制(15、9))SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS valFROM #value AS 公关-- 所有记录放在一起SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS valFROM [值] AS pr联合所有SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01/12 AS DECIMAL(15, 9)), 9) AS val联合所有SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val来自@值联合所有SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS valFROM #value AS 公关删除表#value;删除表 [dbo].[值]；

每一行都得到相同的结果:

<块引用>

0.003541667

补充说明:

您可以通过将硬编码数值填充到变体中来测试它是什么数据类型:

DECLARE @var SQL_VARIANT;选择@var = 4.250选择 SQL_VARIANT_PROPERTY(@var, 'BaseType'),SQL_VARIANT_PROPERTY(@var, 'Precision'),SQL_VARIANT_PROPERTY(@var, 'Scale');

这将在我的本地 SQL Server 框中返回 numeric(4,3).(数字和十进制是

如果您查看属性窗口:

它没有列出这些参数的数据类型，但是通过将值 0.01 和 12 填充到变体中以数据类型 结束相同的技巧numeric(2,2) 和 int 分别.

如果您将第二条语句中的硬编码值转换为这些数据类型:

SELECT * FROM (SELECT ROUND(CAST(val * CAST(0.01 AS NUMERIC(2,2))/CAST(12 AS INT) AS DECIMAL(15, 9)), 9) AS val FROM [值] AS pr) a

这两个语句的结果相同.为什么它决定参数化选择而不是子查询，参数的数据类型实际上是什么，以及硬编码值在第二个语句中被视为正常的数据类型......对我来说仍然是个谜.我们可能需要询问具有 SQL Server 引擎内部知识的人.

During development faced up with quite a strange SQL Server behavior. Here we have absolutely the same formula for absolutely the same number. The only difference is how we are getting this number (4.250). From table, temp table, variable table or hardcoded value. Rounding and casting is absolutely the same in all cases.

-- normal table
CREATE TABLE [dbo].[value]
(
[val] [decimal] (5, 3) NOT NULL
) 
INSERT INTO [value] VALUES (4.250 )
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr

-- inline query from normal table
SELECT * FROM (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a

-- record without table
SELECT ROUND(CAST(4.250 * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val

-- table variable
DECLARE @value AS TABLE (
val  [decimal] (5, 3)
);

INSERT INTO @value VALUES (4.250 )

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value

-- temp table
CREATE TABLE #value
(
    val  [decimal] (5, 3)
)
INSERT INTO #value VALUES (4.250 )
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr

-- all records together
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr
UNION ALL
SELECT ROUND(CAST(4.250 * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM @value
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM #value AS pr

DROP TABLE #value;
DROP TABLE [dbo].[value];

And the results are:

解决方案

This appears to be because you haven't specified the data type of 4.250 everywhere you have hard coded that value, along with mixing datatypes decimal(5,3) and decimal(15,9) in your table declarations and cast statements.

Note that specifying the same precision everywhere:

-- normal table
CREATE TABLE [dbo].[value]
  (
     [val] DECIMAL(15, 9) NOT NULL
  )

INSERT INTO [value]
SELECT CAST(4.250 AS DECIMAL(15, 9))

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   [value] AS pr

-- inline query from normal table
SELECT *
FROM   (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
        FROM   [value] AS pr) a

-- record without table
SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val

-- table variable
DECLARE @value AS TABLE
  (
     val [DECIMAL] (15, 9)
  );

INSERT INTO @value
SELECT CAST(4.250 AS DECIMAL(15, 9))

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   @value

-- temp table
CREATE TABLE #value
  (
     val [DECIMAL] (15, 9)
  )

INSERT INTO #value
SELECT CAST(4.250 AS DECIMAL(15, 9))

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   #value AS pr

-- all records together
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   [value] AS pr
UNION ALL
SELECT ROUND(CAST(CAST(4.250 AS DECIMAL(15, 9)) * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   @value
UNION ALL
SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val
FROM   #value AS pr

DROP TABLE #value;

DROP TABLE [dbo].[value];

You get the same result for every row:

0.003541667

Further Note:

You can test to see what datatype your hardcoded numeric value is by stuffing it into a variant:

DECLARE @var SQL_VARIANT;

SELECT @var = 4.250

SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType'),
       SQL_VARIANT_PROPERTY(@var, 'Precision'),
       SQL_VARIANT_PROPERTY(@var, 'Scale');

This returns numeric(4,3) on my local SQL Server box. (Numeric and Decimal are the same thing )

Edit #2: Further digging

Taking just the first example:

CREATE TABLE [dbo].[value]
(
[val] [decimal] (5, 3) NOT NULL
) 
INSERT INTO [value] VALUES (4.250 )

SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr

-- inline query from normal table
SELECT * FROM (SELECT ROUND(CAST(val * 0.01 / 12 AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a

DROP TABLE VALUE

Having dug a little further, the execution plans are different - the first statement is being parameterised, whereas the subquery version is not:

If you look at the properties window:

It doesn't list the datatypes of these parameters, but doing the same trick with stuffing the values 0.01 and 12 into a variant ends up with datatypes numeric(2,2) and int respectively.

If you cast the hardcoded values in the second statement to those datatypes:

SELECT * FROM (SELECT ROUND(CAST(val * CAST(0.01 AS NUMERIC(2,2)) / CAST(12 AS INT) AS DECIMAL(15, 9)), 9) AS val FROM [value] AS pr) a

You get the same result for both statements. Why it has decided to parameterise the select but not the subquery, what the data types of the parameters actually are, and what datatypes the hardcoded values are treated as normally in the second statement...remain a mystery to me. We would probably need to ask someone with internal knowledge of the SQL Server engine.