Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with real and float


Fun with real and float

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10743 Visits: 12019
Hugo Kornelis (7/1/2013)
[quote][b](By the way, I think real should be considered to have maximum precision six; you should always calculate with at least one more digit then the precision to limit the effect of rounding errors - at least, that's what has been taught to me in high school).

Up that that point I thought your explanation was great, but here you are confusing the precision of representation with the accuracy or results. They aren't the same thing. Besides, your teachers were remiss in telling to use 1 rather than 2 or more - if you want final results accurate to N decimal places all starting values and intermediate results should be represented to a precision of at least N+2 decimal places unless the calculation to be carried out is utterly trivial. It's fine to work with N+1 places when the calculation is something rally very simple like a single multiplication, but if you are trying to compute the eigenvectors or the inverse of a 3 X 3 matrix (both calculations which are extremely common in elementary mechanics) working with N+1 places may give you horribly inaccurate results. 32bit floating point with precision 7 decimal places was intended to carry out complex calculations delivering results to a guaranteed 3 decimal places accuracy - an N+4 design rather than N+1 - and of course less complex calculations to greater guaranteed accuracy.

The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.

Tom

webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3035 Visits: 3755
Yes, thanks to Hugo for the detailed explanation. I agree with those who say that the Microsoft documentation could use Hugo's addition!

I think the following queries show the switchover point where the REAL number is returned as 100.


-- Num1 = 99.99999
CREATE TABLE #QotD ( Num1 REAL, Num2 FLOAT );

INSERT INTO #QotD
VALUES ( 99.9999961, 99.99999999997 );

SELECT *
FROM #QotD;

DROP TABLE #QotD;





-- Num1 = 100
CREATE TABLE #QotD ( Num1 REAL, Num2 FLOAT );

INSERT INTO #QotD
VALUES ( 99.9999962, 99.99999999997 );

SELECT *
FROM #QotD;

DROP TABLE #QotD;



Thanks again.

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5811 Visits: 4718
L' Eomot Inversé (7/1/2013)
. . . The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.

Z1 was built in 1936.

http://en.wikipedia.org/wiki/Konrad_Zuse
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10743 Visits: 12019
Revenant (7/1/2013)
L' Eomot Inversé (7/1/2013)
. . . The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.

Z1 was built in 1936.

http://en.wikipedia.org/wiki/Konrad_Zuse

I should have said "commercial hardware implementation" instead of just "hardware implementation" because I was excluding Z1 (as something that didn't really work, so not an implementation) and Z2 (as something which was only a prototype for the Z3 (and a demonstration machine for the German military), not a properly engineered machine. I don't regard the Z1 as an implementation (because it didn't often work, or so I was told).

Although construction of the Z1 started in 1936, it didn't finish until 1938 and even when finished the machining of its parts wasn't clean/accurate enough so the machine frequently went wrong (or at least so I was taught long ago). There was no working Z1 until Siemens worked with Zuse to build one that actually worked (rather than reconstructing one that worked sometimes) in the late 1980s (a valuable history project, I think).
The Z2 (finished in 1941) was another one-off prototype, with no future - it sprawled over too a large space and was very much a string and sealing-wax piece of engineering - in effect it was the testbed in which some of the Z3s design was verified.
The first working machine, which actually went into production, was the Z3 (also 1941), and that was the machine I meant.
Although it wasn't what we today call an electronic stored program computer, it had a significant electrical component (mechanical relays) and did store program in its electromechanical store.

Zuse had patented pretty well all of what is known as Von Neumann architecture a few years before Williams and Kilburn started developing a reliable non-mechanical store for a stored-program computer which in turn was a year or two before Von Neumann wrote his report. In fact floating point was probably the one area (apart, of course, from politics) where Von Neumann disagreed with Zuse - he was famously anti-floating-point.

Tom

Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5811 Visits: 4718
L' Eomot Inversé (7/1/2013)
Revenant (7/1/2013)
L' Eomot Inversé (7/1/2013)
. . . The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.

Z1 was built in 1936.

http://en.wikipedia.org/wiki/Konrad_Zuse

I should have said "commercial hardware implementation" instead of just "hardware implementation" . . .

Tom, as I said already several times, when you come to Redmond, or to the NW from Portland to Vancouver, we have to get together for drinks and I am buying.
zymos
zymos
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 259
That's an easy one but worthwhile regarding the real and float data types. Thanks.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
Nice question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6277 Visits: 1407
Nice one....



louigopal
louigopal
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 89
Very Nice Explanation
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3687 Visits: 2774
Pretty simple question but what a good discussion. Some great explanation by Hugo. :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search