SQL Server bug or feature? Decimal numbers conversion

Question

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:

Answer 1

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.

Answer 2

If I run:

SELECT  CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)) AS val
,       SQL_VARIANT_PROPERTY(CAST(pr.val * 0.01 / 12 AS DECIMAL(15, 9)), 'BaseType')
FROM    [value] AS pr

Value 0.003541660 is returned.

If I run:

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

Value 0.003541667 is returned.

Smells very much like a bug to me...

edit

Based on the answer by Bridge, I too decided to have a look at the execution plans. Lo and Behold:

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


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

Both queries return 0.003541660. So it appears the reuse of the execution plan is where the 'error' originates. (Note: DBCC FREEPROCCACHE doesn't have the same result!)

Extra note: If I save the execution plans as xml, the files are identical both with and without OPTION (RECOMPILE).

edit:

If I set the database to PARAMETERIZATION FORCED, the subquery is still executed without parameters. If I force parameterization by explicitly using 0.01 and 12 as variables, the returned value is again the same. I think SQL Server defines the parameters in a different datatype than expected. I haven't been able to force the result to 0.003541660 though. This also explains why OPTION(RECOMPILE) results in identical values: If RECOMPILE is used, parameterization is turned off.