Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Exact and Approximate Expand / Collapse
Author
Message
Posted Monday, January 02, 2012 8:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 11,168, Visits: 10,929
Comments posted to this topic are about the item Exact and Approximate



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1229027
Posted Monday, January 02, 2012 8:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 11,168, Visits: 10,929
Surprisingly enough, I got this one right...





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
QotD.png (318 views, 4.57 KB)
Post #1229028
Posted Monday, January 02, 2012 8:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 5,470, Visits: 23,486

Correct answers: 60% (3)
Incorrect answers: 40% (2)
Total attempts: 5



Nice, even if a rather simple question (but it does take one back to the basics and because of that a GOOD QOD) ... and yes I selected the correct answer.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1229033
Posted Monday, January 02, 2012 8:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 20,458, Visits: 14,079
Nice question. Thanks Paul.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1229035
Posted Monday, January 02, 2012 10:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:36 AM
Points: 1,379, Visits: 1,772
This was a good one! Thank-you, Paul. Great start to the year.

Have a Happy & Prosperous New Year 2012, everyone!


Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1229053
Posted Monday, January 02, 2012 10:54 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, February 21, 2014 12:19 AM
Points: 1,716, Visits: 540
Nice Question

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


-----------------
Gobikannan
Post #1229054
Posted Tuesday, January 03, 2012 1:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 3,683, Visits: 4,817
Good "back to Basics" question, Paul
Thanks


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1229080
Posted Tuesday, January 03, 2012 3:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 5,794, Visits: 8,006
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 (base-2 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 front-end tools might not be so smart. And front-end or back-end 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
Post #1229111
Posted Tuesday, January 03, 2012 3:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 11,168, Visits: 10,929
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 fixed-precision types, and in particular to avoid using excessive scale.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1229123
Posted Tuesday, January 03, 2012 3:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 5,794, Visits: 8,006
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
Post #1229125
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse