SQL Server datatype precision - Neo, what is real?

Question

SQL Sever 2000 documentation:

Is a floating point number data with the following valid values: –3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).

SQL Server 2005 documentation:

The ISO synonym for real is float(24).

EDIT: Given what I am reading it says the precision is 7 but I can enter in my database(SQL Server 2005) max of 9 see below, as similarly stated here question no. 7.

Example: 0.180000082

What is the true precision of real and are there configuration options(ie:compatibility modes) that can affect the precision?

Answer 1

You are confusing binary (base 2) precision with decimal (base 10) precision. 24 bits gives you approximately 7 decimal digits.

Answer 2

Your answer is on that same page you linked:

For FLOAT data type, the n is the number of bits used to store the mantissa in scientific notation

24 bits of mantissa gives you (approximately) 7 decimal digits of precision (because 2^24 ~= 10^7).

edit to add:

Notice that everyone keeps saying 'approximately' - this is for a reason :)

Binary floating point numbers and decimal literals do not necessarily play together in an intuitive manner. For background read What Every Computer Scientist Should Know About Floating-Point Arithmetic. Also note that saying 'approximately 7 decimal digits of precision' is not incompatible with being able to store a value with more than 7 significant figures! It does mean however that this datatype will be unable to distinguish between 0.180000082 and 0.180000083, for example, because it isn't actually storing the exact value of either:

declare @f1 real
declare @f2 real

set @f1 = 0.180000082
set @f2 = 0.180000083

select @f1, @f2

select @f1 - @f2

---

------------- -------------
0.1800001     0.1800001

(1 row(s) affected)


-------------
0

(1 row(s) affected)

---

The fact is that real is the same as float(24), a binary floating point number with 24 bits of mantissa, and I don't believe there's a way to change this. Floating-point types are in general not a good choice if you want to store exact decimal quantities.

Answer 3

SQL Server uses either 4 bytes or 8 bytes to store non-decimal floating point numbers.

If you specify real or any value in the range float<1> to float<24>, SQL server uses float<24> internally. If you use float or anything in the range float<25> to float<53> (the limit), it uses float<53> which is otherwise known as double precision.

MSDN documentation for float in SQL Server 2005 is here: float and real (Transact-SQL)

If you have a specific precision need, look instead at using decimal, which provides configurable precision and scale: decimal and numeric (Transact-SQL)