Sql-server – Automatic decimal rounding issue

decimalsql serversql-server-2008

The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals regardless of any cast / converts.

For example, let's do a simple division as 1234/1233. A calculator will produce 1,00081103000811. But when I do this on SQL Server, we get the following:

-- Result: rounded at 1.000811000... with trailing zeroes up until the 37 precision
SELECT CAST(CAST(1234 AS DEC(38,34))/CAST(1233 AS DEC(38,34)) AS DEC(38,37))

-- Result: rounded at 1.000811
SELECT CONVERT(DECIMAL(38,32), 1234)/CONVERT(DECIMAL(38,32),1233)

-- Correct result at 1,00081103000811
-- But this requires the zeroes to be put in manually when you don't
-- even know the precision of the end result
SELECT 1234.0/1233.00000000000000

Why does this automatic rounding occur? And what's the best way to calculate insanely long decimal values when you can't be sure how big a number (the int or dec part) will be, since the table can contain various different values?

Thanks!

Best Answer

tl;dr

Don't do calculations in SQL language

Longer

The result scale and precision is well defined here on MSDN. It isn't intuitive, really. However, in simple terms, precision is lost when the input scales are high because the result scales need to be dropped to 38 with a matching precision drop.

To confirm things

Your extra CAST in the first example simply add zeroes
The truncation happens as per my MSDN link (2nd example)
The 3rd example with constants has implied decimal values that are just enough (5,1) and 18,14).
This means the result scale and precision have no truncation (see blow)

Related Solutions

Sql-server – Why doesn’t `1` fit into a decimal(4, 4) column

The Precision specifies the total number of digits that can be stored, the Scale specifies, how many of those digits live behind the decimal point.

A DECIMAL(4,4) therefore has four digits and all are behind the decimal point. That means you can store values form -0.9999 to 0.9999. You cannot store -1 or 1 as they have a digit before the decimal point.

SQL Fiddle

Query 1:

SELECT CAST(-0.9999 as DECIMAL(4,4)),CAST(0.123456 as DECIMAL(4,4)),CAST(0.9999 as DECIMAL(4,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -0.9999 |   0.1235 |   0.9999 |

To store 1 you have to add an additional digit as in DECIMAL(5,4). That allows you to store values from -9.9999 to 9.9999:

SQL Fiddle

Query 2:

SELECT CAST(-9.9999 as DECIMAL(5,4)),CAST(1 as DECIMAL(5,4)),CAST(9.9999 as DECIMAL(5,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -9.9999 |        1 |   9.9999 |

Sql-server – Handling sql column with mixed varchar and float data types

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255).

According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal.

Add one more cast to nvarchar(255) in the first branch of the CASE.

SELECT
CASE 
    WHEN [Parcel Number 1] like '%E+%'
    THEN CAST(CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
    ELSE [Parcel Number 1]
END
FROM XLSimport