Collections:

Precision and Rounding of FLOAT Values in SQL Server Transact-SQL

Q

How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL?

✍: FYIcenter.com

A

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 Transact-SQL 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; ------------ 9.234568E+10 -- 7 digits precision -- Single precision with rounding DECLARE @x REAL; -- FLOAT(24) SET @x = 9.234567890E+10; SELECT @x; ------------ 9.234568E+10 -- 7 digits precision -- FLOAT(25) works like FLOAT(53) DECLARE @x FLOAT(25); SET @x = 9.2345678901234567890E+100; SELECT @x; --------------------- 9.23456789012346E+100 -- 15 digits precision -- Double precision with rounding DECLARE @x FLOAT(53); SET @x = 9.2345678901234567890E+100; SELECT @x; --------------------- 9.23456789012346E+100 -- 15 digits precision

In other words, Transact-SQL is not truly respecting FLOAT(n) declaration.

**⇒** Underflow and Overflow of FLOAT Values in SQL Server Transact-SQL

**⇐** Approximate Numeric Data Types in SQL Server Transact-SQL

2017-04-19, 10783🔥, 0💬

Popular Posts:

Where to find answers to frequently asked questions I am new to Oracle database. Here is a list of f...

What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...

How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...

How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...

What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...