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.

2017-04-19, 1207👍, 0💬

Popular Posts:

How to put statements into a statement block in SQL Server Transact-SQL? You can put statements into...

How To Count Duplicated Values in a Column in SQL Server? If you have a column with duplicated value...

How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...

Where to find answers to frequently asked questions on Oracle basic concepts? I am new to Oracle dat...

How to put statements into a statement block in SQL Server Transact-SQL? You can put statements into...