The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,842 other followers

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: