floating point – SQL Server: Calculation with numeric literals requires to cast to obtain the right precision (via: Stack Overflow)
Posted by jpluimers on 2013/12/24
This has bitten me so many times, so I’m glad I found the below question/answers on StackOverflow.
When you perform calculations in SQL Server involving numeric literals, you have to take into account which precision you want your result to be, and CAST/CONVERT the literals accordingly.
The reason is condensed to this statement by Lieven Keersmaekers:
SQL Server uses the smallest possible datatype.
He follows with examples to view the actual representation of a literal/expression using SQL_VARIANT_PROPERTY (which has been there since at least SQL Server 2000).
SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType') SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision') SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale') SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')
He then explains the precision in the calculation of the original question showing what went wrong when you don’t perform casts.
Finally he quotes the Microsoft documentation which – for SQL Server 2012 – has been moved to:
Converting decimal and numeric Data
For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.
In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.
Finally Alex Kuznetsov provides a link to a blog entry he wrote on casting, which gives a bit more insight in the deceptive ease with which things can go wrong: Alexander Kuznetsov : For better precision cast decimals before calculations.
Finally I found this interesting link on SQL Server constant literals: SQL- Datatypes and Literals – Web Design & SEO Company.
Great insight guys!
–jeroen
via: floating point – SQL Server: Calculation with numeric literals – Stack Overflow.
The Wiert Corner - irregular stream of stuff 
Leave a Reply