Float & Real Data Types in SQL Server uses the floating-point number format. Real is a Single Precision Floating Point number, while Float is a Double Precision Floating Point number. The Floating point numbers can store very large or very small numbers than decimal numbers. But this trade-off comes at the cost of precision. In this tutorial let us look at them in detail and learn the difference between float & decimal or numeric data types.
Table of Contents
What is Floating point numbers
The floating-point numbers do not have a fixed decimal point. The decimal point can appear anywhere in the number i.e. it floats. Hence it is known as Floating Point numbers. The behavior of float and real follows the IEEE Standard for Floating-Point Arithmetic
The floating-point numbers are stored using the scientific notation in binary format. Scientific notation is a way of writing very large or very small numbers. It is written in the following format.
For Example, 650,000,000 can be written in scientific notation as 6.5 ✕ 10^8, where 6.5 is Significand, 10 is the base and 8 is exponent.
The Floating Point numbers can be either 8 bytes ( 64 bit or Double Precision ) or 4 Bytes ( 32 bit or Single Precision).
Float & Real in SQL Server
The SQL Server has two data types Float & Real which stores the Floating Point numbers.
Float Data type is Double Precision 64-bit format. It uses 8 bytes of Storage
Real Data Type is Single Precision 32-bit format. It uses 4 bytes of Storage
The binary representation of these numbers is as shown below.
The first bit is a Sign bit that indicates whether the number is positive or negative. The 0 stands for positive. The exponent is either 11 bit (Float) or 8 bit (Real). The Significand uses the remaining places. 52 Bits in case of float data type & 23 bits in case of Real Data Type
Creating Float & Real Columns
The syntax for creating a float column float(n), when n is between 1 to 53. The default value of n is 53.
The float(1) to float(23) will create the Single Precision 32-bit column, which is actually Real data type. Hence SQL Server automatically maps it into Real data type.
Float(24) to float(53) will create the Double Precision 64-bit column, Hence they become simply float.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | create table testFloat ( col10 float(10), col20 float(20), col23 float(23), col24 float(24), col25 float(25), col30 float(30), colF float, colR real, ) |
As you can see, the n is removed from the column definitions.
Decimal Vs Floating Point number
The decimal & Floating point number can store decimal numbers, Hence it is very important to know the difference between them.
| Decimal | Float |
|---|---|
| Position of Decimal place is fixed. | Position of Decimal place is not fixed |
| Maximum precision that you can handle is 38 digits (including the decimal points) | Can handle large number of data |
| Results are more accurate compared to the float. | Arithmetic operations can result in loss of precision |
| decimals data values are stored exactly as specified. | Uses Double Precision 64-bit format or Single Precision 32 bit format to store data. This results in approximation of the stored value. |
| Requires more storage space | Requires less storage space |
| Since the result is exact, you can use them equality checks (= & <> operators), rounding of numbers, etc. As long as the result stays within the max & min value allowed. | Avoid using float equality checks (= & <> operators), rounding of numbers, etc. Also, avoid using them in application like financial applications where accuracy is important |
Floating-point can handle a large number of data
For Example, a decimal(9,2) data type uses 5 bytes of storage but can store up to -9999999.99 to 9999999.99.
The Int data types use 4 bytes and can store numbers from -2,147,483,648 to 2,147,483,647. But we cannot use it to store fractions.
The 4 bytes of floating point number ( Real data type, Single Precision 32 bit format ) can store value between between -340,000,000,000,000,000,000,000,000,000,000,000,000 and 340,000,000,000,000,000,000,000,000,000,000,000,000.
Loss of Precision
The large number under less storage that a floating-point number can handle comes at the cost of precision.
First Create a table with a float column
1 2 3 4 5 6 7 8 9 10 | create table testFloat ( colFloat float, ) |
Now insert sum of .1 + .2 into that column. We use the CAST function to cast the numbers to float so that SQL server Floating Point arithmetic.
1 2 3 4 5 6 | insert into testFloat (colFloat) values ( CAST(.1 as FLOAT) + CAST( .2 as FLOAT)) |
Query and check if the value is inserted.
1 2 3 4 5 6 | select * from testFloat *Result 0.3 |
Now, retrieve the number using a where condition. The query will not retrieve any records
1 2 3 4 5 6 7 | select * from testFloat where colFloat= .3 select * from testFloat where colFloat= cast(.3 as float) ***Result No Records |
This is because the .1 is stored as approximately as 0.100000001490116119384765625 (in 32-bit single precision). You can refer to the Convertor
The loss of precision can also happen in Decimal also. But it happens only in case the final result or intermediate result exceeds the limits for the decimal datatype.
Which one to Use
Decimal is preferred if the number stays below the maximum precision provided by the decimal, which is 38.
But to handle the large numbers you may have to use the real or float
Also, if the storage space is main criteria, then use real data type, which takes only 4 bytes. Provided the loss accuracy is ok.
Avoid using float equality checks (=), inequality (<>) checks etc, rounding of numbers, etc. Also, avoid using them in application like financial applications where accuracy is important.
References
Read More
Man this is one awesome article on topic. Thank you!
Thank you!