473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Precision in a REAL data type

Tim
Folks,

Can anyone thow some clarifying light on the following?

I have come across a column with the same name and same data contents
defined on different tables, on some the column is defined as a FLOAT
on others it is a REAL.
(Don't ask me why, it's inherited, legacy and due for removal once we
have absorbed all the good bits into the data warehouse). .

[BreakDown_Hours] [real] NULL,
[BreakDown_Hours] [float] NULL,

So far so good, according to the documentation REAL is basically a 4
Byte float and equivlent to FLOAT(24).

Reading the documentation it clearly states that the 'Precision' of a
REAL is 7.
As I recall the definition of 'precision' that means that it is
capable of upto 7 decimal points.
Now it may well be I have that wrong, if so feel free to correct me.

When I started looking at the data in the columns defined as a REAL I
found upto 9 decimal places albeit with 2 leading zeros.

0.003305263
0.003305383
0.003305879
0.003306
0.003306305
0.003306702
0.003307051
0.003307974
0.003308263
0.003308823
0.003308901

Looking at the properties of the column in Management studio it states
the 'Numeric Precision' as 24

I can get round all of this without too much trouble but I'd like to
understand what is going on.
Does anyone have an explanation?

TIA Tim


Nov 13 '08 #1
2 7303
Tim (ti**********@hotmail.com) writes:
Reading the documentation it clearly states that the 'Precision' of a
REAL is 7.
As I recall the definition of 'precision' that means that it is
capable of upto 7 decimal points.
Now it may well be I have that wrong, if so feel free to correct me.

When I started looking at the data in the columns defined as a REAL I
found upto 9 decimal places albeit with 2 leading zeros.
Keep in mind that a floating-point number also has a mantissa, which in
this example apparently is -2.

Books Online gives this range for real:

-3.40E+38 to -1.18E-38, 0 and 1.18E-38 to 3.40E+38

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 13 '08 #2
On Thu, 13 Nov 2008 07:02:12 -0800 (PST), Tim wrote:
>Folks,

Can anyone thow some clarifying light on the following?
Hi Tim,

In addition to Erland's reply, here are a few othe rpoints to keep in
mind:

1) What you see on your screen is how the front end chooses to render
the numbers. Not all clients use the same number of digits when
displaying a floating point number. One way to get a reliable look at
the real precision is to cast the number to e.g. DECIMAL(38,30) (unless
the number is too big or too small to fit is the range of fixed point
numbers.

2) Precision of a floating point number is measured in "significant
figures", i.e. it's calculated from the first non-zero number. So the
numbers 123000000, 123, 1.23, and 0.0000123 all have a precision of
three significant figures.

3) Floating point numbers are actually stored in some binary format.
Some numbers that can be represented as an exact number in decimal can
not be in binary (just as 1/3 can not be represented exactly in
decimal). The actual precision is based on the internals of the actual
storage; the precision quoted in Books Online is an approximation (and I
believe it's a "safe" approximation, i.e. you will get at least that
precision for any number - but don't take my word for this, I'm not 100%
sure).

Does this answer all your questions?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Nov 15 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Roger Leigh | last post by:
Hello, I'm writing a fixed-precision floating point class, based on the ideas in the example fixed_pt class in the "Practical C++ Programming" book by Steve Oualline (O' Reilly). This uses a...
3
by: James Foreman | last post by:
I'm using DB2 UDB 8.1.2 on Suse linux. Try this: CREATE TABLE testsums (cost_of_sale decimal(7,2), price decimal(9, 2)); INSERT INTO testsums VALUES (10000, 30000); SELECT cost_of_sale/price...
15
by: michael.mcgarry | last post by:
Hi, I have a question about floating point precision in C. What is the minimum distinguishable difference between 2 floating point numbers? Does this differ for various computers? Is this...
15
by: Donkey | last post by:
Hi, The precision of built-in date type of C is very low. Even using long double float type or double float type, we can only use 12 or 16 digits after the decimal point. What can we do if we want...
3
by: Boot2TheHead | last post by:
This one cost me a solid half hour yesterday. I'm wondering why on earth the default precision for a decimal type is 18,0. Maybe I'm mistaken. A decimal datatype sort of implies that you'd want...
10
by: Artemio | last post by:
Hello all! I just stumbled across a weird problem with precision of a division operation. I am on Mac OS X, GCC 4.0.1. Say I have two float or double numbers, and I want to divide one by...
10
by: Bo Yang | last post by:
Hi, I am confused by the double type in C++. I don't know whether below is legal or possible: double PI = 3.141592675932; Now, can I get another double variable from PI with lower precision,...
137
by: mathieu.dutour | last post by:
Dear all, I want to do multiprecision floating point, i.e. I want to go beyond single precision, double precision and have quadruple precision, octuple precision and the like, and possibly with...
6
by: Matthew | last post by:
Hi, I want to change the precision level of floating point variables and calculations which is done in php.ini. However the server I rent for my domain does not give me access to php.ini,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.