SQL Server 错误或功能?十进制数转换 [英] SQL Server bug or feature? Decimal numbers conversion

问题描述

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

--普通表创建表 [dbo].[值]([val] [decimal] (5, 3) NOT NULL)插入 [值] 值 (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) a-- 无表记录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) 非空)插入 [值]SELECT CAST(4.250 作为 DECIMAL(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 [value] 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));插入@valueSELECT CAST(4.250 作为 DECIMAL(15, 9))SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS val从@value-- 临时表创建表#value(val [十进制] (15, 9))插入#valueSELECT CAST(4.250 作为 DECIMAL(15, 9))SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS valFROM #value AS pr-- 所有记录放在一起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从@value联合所有SELECT ROUND(CAST(val * 0.01/12 AS DECIMAL(15, 9)), 9) AS valFROM #value AS pr删除表#value;删除表 [dbo].[值];

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

<块引用>

0.003541667

进一步说明:

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

DECLARE @var SQL_VARIANT;选择@var = 4.250SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType'),SQL_VARIANT_PROPERTY(@var, '精度'),SQL_VARIANT_PROPERTY(@var, '比例');

这会在我的本地 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.

这篇关于SQL Server 错误或功能?十进制数转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！