MSDN Documentation

SQL FLOAT and REAL Data Types

The FLOAT and REAL data types are used for approximate-number data values. These are non-exact, variable-precision numeric data types. They are used for numbers that may not be exactly representable, such as scientific measurements or calculations that involve fractions.

General Information

FLOAT and REAL are functionally the same in SQL Server. They are both implemented as non-exact numeric data types. The difference between them lies in the precision and storage size:

  • REAL: Implemented as a 32-bit (4-byte) floating-point number. It supports approximately 7 decimal digits of precision.
  • FLOAT: Can be specified with a precision from 1 to 53 bits.
    • If precision is specified as 1 through 24, it's implemented as a 32-bit (4-byte) REAL.
    • If precision is specified as 25 through 53, it's implemented as a 64-bit (8-byte) double-precision floating-point number.

When you declare a FLOAT without specifying precision, it defaults to 53 bits of precision (64-bit).

Syntax

You can declare these types as follows:


DECLARE @myReal REAL;
DECLARE @myFloat FLOAT;
DECLARE @myFloat24 FLOAT(24);
DECLARE @myFloat53 FLOAT(53);

Range and Precision

The range and precision of FLOAT and REAL types are important to understand when dealing with very large or very small numbers, or when exactness is critical.

REAL (32-bit)

  • Approximate Range: ±1.17549435 x 10-38 to ±3.40282347 x 1038
  • Approximate Precision: 7 decimal digits

FLOAT(n)

  • FLOAT(1) to FLOAT(24) (implemented as REAL)
    • Approximate Range: ±1.17549435 x 10-38 to ±3.40282347 x 1038
    • Approximate Precision: 7 decimal digits
  • FLOAT(25) to FLOAT(53) (implemented as 64-bit double-precision)
    • Approximate Range: ±2.2250738585072014 x 10-308 to ±1.7976931348623157 x 10308
    • Approximate Precision: 15 decimal digits

When to Use FLOAT and REAL

These data types are suitable for:

  • Storing numbers that are inherently approximate, such as scientific measurements, experimental data, or results of complex calculations.
  • Situations where storage space is a concern and perfect accuracy is not required.
  • Comparisons where a small tolerance for error is acceptable.

Important Note on Accuracy

Due to their nature, floating-point numbers can sometimes have rounding errors. This means that a value might not be stored exactly as you input it. For critical financial calculations or situations requiring exact precision, consider using DECIMAL or NUMERIC data types instead.

Example Usage

Let's see how these types behave:


-- Declare variables
DECLARE @temp_celsius REAL = 25.55;
DECLARE @pi_approx FLOAT = 3.141592653589793;
DECLARE @large_value FLOAT(53) = 1.2345678901234567890123456789012345678901234567890123;

-- Output the values
PRINT 'Temperature in Celsius: ' + CAST(@temp_celsius AS VARCHAR(20));
PRINT 'Approximation of Pi: ' + CAST(@pi_approx AS VARCHAR(50));
PRINT 'Large value with high precision: ' + CAST(@large_value AS VARCHAR(100));

-- Example showing potential rounding
DECLARE @fraction FLOAT = 1.0 / 3.0;
PRINT '1.0 / 3.0 (FLOAT): ' + CAST(@fraction AS VARCHAR(20));

DECLARE @fraction_exact DECIMAL(10, 9) = CAST(1 AS DECIMAL(10, 9)) / CAST(3 AS DECIMAL(10, 9));
PRINT '1.0 / 3.0 (DECIMAL): ' + CAST(@fraction_exact AS VARCHAR(20));

Considerations

  • Comparisons: Direct equality comparisons (=) with floating-point numbers can be unreliable due to precision issues. It's often better to check if the difference between two numbers is within a small tolerance.
  • Storage: While REAL (4 bytes) is more space-efficient than a 64-bit FLOAT (8 bytes), the increased precision of the 64-bit version might be necessary for certain applications.
  • Performance: Floating-point operations can sometimes be faster than fixed-point operations, especially on hardware designed for them, but this varies by system and workload.

Alternatives

For exact numeric values, especially in financial applications, consider using:

  • DECIMAL / NUMERIC: These are exact numeric data types that store numbers precisely as specified. You define both precision (total number of digits) and scale (number of digits after the decimal point).

This documentation provides a foundational understanding of the FLOAT and REAL data types in SQL. Always choose the data type that best suits the precision and accuracy requirements of your data.