

SSCrazy Eights
Group: General Forum Members
Last Login: Tuesday, June 21, 2016 1:36 PM
Points: 9,932,
Visits: 11,340





SSCrazy Eights
Group: General Forum Members
Last Login: Tuesday, June 21, 2016 1:36 PM
Points: 9,932,
Visits: 11,340





SSCertifiable
Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333,
Visits: 25,280





SSCoach
Group: General Forum Members
Last Login: Wednesday, June 22, 2016 2:24 PM
Points: 19,803,
Visits: 18,086





SSCommitted
Group: General Forum Members
Last Login: Monday, June 20, 2016 5:04 AM
Points: 1,642,
Visits: 2,008





SSCrazy
Group: General Forum Members
Last Login: Tuesday, April 5, 2016 12:40 AM
Points: 2,158,
Visits: 608


Nice Question
Correct answers: 63% (15) Incorrect answers: 38% (9) Total attempts: 24
 Gobikannan




SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 5,111,
Visits: 6,833


Good "back to Basics" question, Paul Thanks
____________________________________________ Space, the final frontier? not any more... All limits henceforth are selfimposed. “libera tute vulgaris ex”




SSCertifiable
Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 7,645,
Visits: 10,600


Happy new year everyone! ;)
It doesn't happen very often, but today I find myself in disagreement with Paul. The correct answer is (or should be) DECIMAL.
The "precision" mentioned on the BOL page about floating point data is not the number of decimal positions represented internally, but the number of decimal positions that can be trusted to be correct. The internal representation is different (base2 to be exact). As mentioned on the top of the BOL page: "not all values in the data type range can be represented exactly". The value 0.1234567 is one of the many values that can not be represented exactly. When trying the code snippet in SSMS, this is carefully hidden  because SSMS knows about the number of "trustworthy" positions for the REAL data type and shows only that number of positions. Other frontend tools might not be so smart. And frontend or backend applications that do further calculations on the number do get the internal representation, which in the case of the REAL data is not exactly correct.
Here is a modification of Paul's code that tricks SSMS into revealing a more accurate representation of the values internally stored; it shows that the DECIMAL value is indeed exact, but the REAL value is not.
SELECT [decimal] = CAST(@n1 * @n2 AS decimal(38,36)), [real] = CAST(@n3 * @n4 AS decimal(38,36))
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis




SSCrazy Eights
Group: General Forum Members
Last Login: Tuesday, June 21, 2016 1:36 PM
Points: 9,932,
Visits: 11,340


Hugo Kornelis (1/3/2012) It doesn't happen very often, but today I find myself in disagreement with Paul. The correct answer is (or should be) DECIMAL... Some good points, and it is true (for example) that Query Analyzer against a SQL Server 2000 instance gives the result using REAL as 0.12345671 but no matter how you CAST the result, the result of the DECIMAL calculation is 0.123457 (i.e. rounded to six decimal places) so the REAL result is, in the wording of the question ("In the example below, which data type will give the most accurate answer?") more accurate. I don't see how the answer could possibly be DECIMAL (0.123457 decimal is much further from 0.1234567 (correct) than 0.12345670908689499 (real, unadjusted for precision))?
Using REAL in this case does give exactly the right displayed answer in SSMS (though not in other tools as you say) and when working with approximate data types we do need to be careful about the significant digits (as demonstrated by SSMS). Anyway, the main point of the question is for people to be aware of the limitations of the fixedprecision types, and in particular to avoid using excessive scale.
Paul White SQLPerformance.com SQLblog.com @SQL_Kiwi




SSCertifiable
Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 7,645,
Visits: 10,600


OOPS!! Sorry, Paul  you are completely right.
I had missed the rounding of the decimal result; I thought that the decimal result was the exact and correct result. Even when running the code I posted above (that shows the extra decimals), I simply overlooked that the DECIMAL result was 0.123457 instead of 0.1234567. My bad; my apologies.
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis



