Float & Real Data Types in SQL Server

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.

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.

Scientific Notation
Scientific Notation

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.

Float Double Precision and Real is Single Precision Floating Point Format

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.

As you can see, the n is removed from the column definitions.

Creating Float and Real Columns in SQL Server

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.

DecimalFloat
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 spaceRequires 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

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.

Query and check if the value is inserted.

Now, retrieve the number using a where condition. The query will not retrieve any 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

float and real (Transact-SQL)

Read More

2 thoughts on “Float & Real Data Types in SQL Server”

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top