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

[Erland Sommarskog]

Tim (ti**********@h otmail.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****@sommarsk og.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

[Hugo Kornelis]

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 "significan t
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