SQL Server FAQ - Precision and Rounding on REAL and FLOAT Literals

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How REAL and FLOAT Literal Values Are Rounded?

By definition, FLOAT(n) should store the mantissa of the floating number in n bits. For example, FLOAT(16) should have a precision one-byte less than FLOAT(24).

However, SQL Server 2005 only supports two precisions for floating numbers:

Single Precision: FLOAT(24) or REAL, stored in 4 bytes, giving about 7 digits of precision, covering all types from FLOAT(1) to FLOAT(24),
Double Precision: FLOAT(53), stored in 8 bytes, giving about 15 digits of precision, covering all types from FLOAT(25) to FLOAT(53).

The tutorial exercise below shows you some different precision and rounding examples:

-- FLOAT(1) works like FLOAT(24) 
DECLARE @x FLOAT(1)
SET @x = 9.234567890E+10;
SELECT @x;
GO
9.234568E+10 -- 7 digits precision

-- Single precision with rounding 
DECLARE @x REAL; -- FLOAT(24)
SET @x = 9.234567890E+10;
SELECT @x;
GO
9.234568E+10 -- 7 digits precision

-- FLOAT(25) works like FLOAT(53)
DECLARE @x FLOAT(25);
SET @x = 9.2345678901234567890E+100;
SELECT @x;
GO
9.23456789012346E+100 -- 15 digits precision

-- Double precision with rounding
DECLARE @x FLOAT(53);
SET @x = 9.2345678901234567890E+100;
SELECT @x;
GO
9.23456789012346E+100 -- 15 digits precision

(Continued on next topic...)