March 19, 2019 at 1:52 pm
Hi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?
/Jacob
March 19, 2019 at 2:12 pm
jacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
Here's what I get on various versions
2017 484.6999999999999886
2016 484.6999999999999886
2014 484.6999999999999900
2012 484.6999999999999900
2008r2 484.6999999999999900
2005 484.6999999999999900
Why not just 484.7? Because that's how you have sized the value. A numeric (32,16) will produce that response.
This produces 484.70
SELECT cast (4.847E2 as numeric(32,2))
If you notice, the new versions provide a higher level of precision.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 19, 2019 at 2:14 pm
jacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
A simple answer is that 4.847E2 is an approximate numeric data type and as such it can't be fully precise.
March 19, 2019 at 2:22 pm
Michael L John - Tuesday, March 19, 2019 2:12 PMjacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
Here's what I get on various versions
2017 484.6999999999999886
2016 484.6999999999999886
2014 484.6999999999999900
2012 484.6999999999999900
2008r2 484.6999999999999900
2005 484.6999999999999900Why not just 484.7? Because that's how you have sized the value. A numeric (32,16) will produce that response.
This produces 484.70
SELECT cast (4.847E2 as numeric(32,2))If you notice, the new versions provide a higher level of precision.
Thanks ðŸ˜
I should have been more clear I ment why not 484,7000000000000000 and that did Luis answer ðŸ‘
March 19, 2019 at 2:23 pm
Luis Cazares - Tuesday, March 19, 2019 2:14 PMjacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
A simple answer is that 4.847E2 is an approximate numeric data type and as such it can't be fully precise.
Thanks for your answer.
Do you know how it's calculated on different versions?
March 19, 2019 at 2:52 pm
jacob.saugmann - Tuesday, March 19, 2019 2:23 PMLuis Cazares - Tuesday, March 19, 2019 2:14 PMjacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
A simple answer is that 4.847E2 is an approximate numeric data type and as such it can't be fully precise.
Thanks for your answer.
Do you know how it's calculated on different versions?
If I did, I would probably be working at Microsoft. 😀
March 20, 2019 at 7:14 am
jacob.saugmann - Tuesday, March 19, 2019 2:23 PMLuis Cazares - Tuesday, March 19, 2019 2:14 PMjacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
A simple answer is that 4.847E2 is an approximate numeric data type and as such it can't be fully precise.
Thanks for your answer.
Do you know how it's calculated on different versions?
It's a matter of treating 4.847E2 as a FLOAT, and floating point representation simply can NOT HANDLE anything that has 7 tenths. Forcing it into a numeric precision of (32,16) just gets you ever more precise representation of the floating point value it starts with, which was computed by trying to use pure binary to represent that number, which simply isn't possible to exactly and precisely reproduce with nothing but binary floating point representation. That's why all you see is ever so slightly closer to accurate, the further along the version of SQL Server. It will NEVER be 100% accurate, as long as scientific notation is considered a float value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 20, 2019 at 9:58 am
(pasting a reply from a similar question from last year)
The behavior changed in SQL 2016:
https://support.microsoft.com/en-us/help/4010261/sql-server-and-azure-sql-database-improvements-in-handling-some-data-t
If you run queries on a SQL 2017 database set to compatibility level 120 (SQL 2014) or less, it will produce the same output as when run on SQL 2014:CREATE DATABASE Testr
GO
USE Testr
GO
ALTER DATABASE Testr SET COMPATIBILITY_LEVEL = 130 WITH ROLLBACK IMMEDIATE
GO
SELECT convert(numeric(38,12),cast('1234567891230000000' AS float)/1000000000)
-- 1234567891.229999780655
GO
ALTER DATABASE Testr SET COMPATIBILITY_LEVEL = 120 WITH ROLLBACK IMMEDIATE
GO
SELECT convert(numeric(38,12),cast('1234567891230000000' AS float)/1000000000)
-- 1234567891.229999800000
GO
Eddie Wuerch
MCM: SQL
March 20, 2019 at 11:11 am
jacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
484.7 in binary has recurring digits after the binary point: 111100100.1011001100110011001100110011...
So it cannot be stored exactly in a floating point mantissa. The more digits the floating point mantissa has the closer it will get to 484.7
The difference between the result from SQL 2016 and SQL 2012/14 is 0.0000000000000014, in binary this is represented as about 49 zeros before the 1's start. So it looks like SQL 2016 has a longer mantissa.
To work around this you can convert 4.84E2 to a string before converting to a decimal:
SELECT convert(numeric(32,16),convert(varchar,4.847E2))
March 20, 2019 at 11:31 am
Jonathan AC Roberts - Wednesday, March 20, 2019 11:11 AMjacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
484.7 in binary has recurring digits after the binary point: 111100100.1011001100110011001100110011...
So it cannot be stored exactly in a floating point mantissa. The more digits the floating point mantissa has the closer it will get to 484.7
The difference between the result from SQL 2016 and SQL 2012/14 is 0.0000000000000014, in binary this is represented as about 49 zeros before the 1's start. So it looks like SQL 2016 has a longer mantissa.To work around this you can convert 4.84E2 to a string before converting to a decimal:
SELECT convert(numeric(32,16),convert(varchar,4.847E2))
Thanks all
Thanks for that explaination ðŸ‘
March 20, 2019 at 12:52 pm
There is no difference in the underlying value, the double data type is still using the same IEEE standard format with the same number of mantissa bits. The difference is that the conversion from floating point to decimal is slightly more accurate in SQL 2016.
The database engine in Microsoft SQL Server 2016 and Azure SQL Database includes improvements in data type conversions and several other operations. Most of these improvements offer increased precision when you work with floating-point types and also with classic datetime types.
You can look at the raw binary in both versions:SELECT cast (4.847E2 as BINARY(8))
That number is stored as 0x407E4B3333333333 in SQL 2016, 2014, ..., 2005, and possibly even further back.
March 20, 2019 at 12:58 pm
Scott Coleman - Wednesday, March 20, 2019 12:52 PMThere is no difference in the underlying value, the double data type is still using the same IEEE standard format with the same number of mantissa bits. The difference is that the conversion from floating point to decimal is slightly more accurate in SQL 2016.The database engine in Microsoft SQL Server 2016 and Azure SQL Database includes improvements in data type conversions and several other operations. Most of these improvements offer increased precision when you work with floating-point types and also with classic datetime types.
You can look at the raw binary in both versions:
SELECT cast (4.847E2 as BINARY(8))
That number is stored as 0x407E4B3333333333 in SQL 2016, 2014, ..., 2005, and possibly even further back.
Thanks, It sense makes sense that it's the conversion that's causing the difference and not the storage as they should be using the IEEE ISO standards for storing floating point.
March 20, 2019 at 7:16 pm
jacob.saugmann - Tuesday, March 19, 2019 1:52 PMHi, i got a question today and I just can figure out an answer.
When casting a Sceientific notation to Numeric(32,16) the cast gives different results on different SQL server versions.
The following query:
SELECT cast (4.847E2 as numeric(32,16))
On SQL2012/14 gives: 484.6999999999999900
On SQL 2016 it gives: 484.6999999999999886
Why not just 484.7?
Can anybody explain what’s going on?/Jacob
Yes - you're casting something with 3 significant digits to something with at a minimum 16 digits. Why is either notation or the assumption that it would be 484.7000000000000 any more or less accurate? Each represent the represent the value with the appropriate level of accuracy, so frankly any one of those as well as a bunch of others are equally valid.
No - you don't simply gain accuracy by simply casting to a numeric style with a larger precision.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply