SQL Server: Calculation with numeric literals

sql cast as decimal
sql cast as decimal 2 places
sql data types
sql numeric
sql server convert
sql cast date
sql convert varchar to numeric
sql convert to decimal with 2 decimal places

I did some testing with floating point calculations to minimize the precision loss. I stumbled across a phenomen I want to show here and hopefully get an explanation.

When I write

print 1.0 / (1.0 / 60.0)

the result is

60.0024000960

When I write the same formula and do explicit casting to float

print cast(1.0 as float) / (cast(1.0 as float) / cast(60.0 as float))

the result is

60

Until now I thought that numeric literals with decimal places are automatically treated as float values with the appropriate precision. Casting to real shows the same result as casting to float.

  • Is there some documentation on how SQL Server evaluates numeric literals?
  • Of what datatype are those literals?
  • Do I really have to cast them to float get better precision (which sounds like irony to me :)?
  • Is there an easier way than cluttering my formulas with casts?

SQL Server uses the smallest possible datatype.

When you run this script

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')

you'll see that SQL Server implicitly used a NUMERIC(2, 1) datatype. The division by 60.0 converts the result to NUMERIC(8, 6). The final calculation converts the result to NUMERIC(17, 10).


Edit

Taken from SQL Server Books Online Data Type Conversion

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.

MySQL 5.7 Reference Manual :: 9.1.2 Numeric Literals, Number literals include exact-value (integer and DECIMAL ) literals and The FLOAT and DOUBLE data types are floating-point types and calculations are  Numeric Literals. Numeric literals are used when numeric data values are stored in character strings. For conversion of numeric SQL data to an SQL_C_CHAR string, or conversion of numeric C data to an SQL_CHAR or SQL_VARCHAR string, numeric literal syntax is used to specify what is stored in the target. For conversion of a numeric stored as a SQL_C_CHAR string to numeric SQL data, or a numeric stored as a SQL_CHAR string to numeric C data, this syntax is used to validate what is stored in the

Yes, you frequently have to cast them to float get better precision. My take on it:

For better precision cast decimals before calculations

Chapter 3 – Numbers, In SQL, a number – i.e.: any signed, or unsigned, combination of the digits 0 to 9 – is An <exact numeric literal> is either an integer or a decimal number and its calculating the check digit, which is usually based on a substring extraction. 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') you’ll see that SQL Server implicitly used a NUMERIC (2, 1) datatype. The division by 60.0 converts the result to NUMERIC (8, 6).

I think it should be understood what is going on behind the scenes for future reference in similar cases.

Literal numerical values with decimal point excluding scientific notation represent Decimal data type which is stored as smallest possible Decimal type. Same quote as Lieven Keersmaekers's from: https://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal

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.

The trailing zeros on the right of decimal point specify scale. The leading zeros left of decimal point are ignored.

Some examples:

1.0  -> Decimal(2,1)
60.0 -> Decimal(3,1)
1.00 -> Decimal(3,2)
01.0 -> Decimal (2,1)

Another point to consider is Data Type precedence. When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. And yet another point to consider is if we do arithmetic operations on Decimal types that the resulting Decimal type, i.e. precision and scale depend on both operands and operation itself. This is described in document Precision, Scale, and Length.

So, part of your expression in parenthesis

( 1.0 / 60.0 ) is evaluated to 0.016666 and the resulting type is Decimal (8,6)

using above rules about Precision and scale of Decimal expressions. In addition the banker's rounding or rounding to even is used. It is important to note different rounding for Decimal and float type are used. If we continue the expression

1.0 / 0.016666 is evaluated to 60.002400096 and the resulting type is Decimal (17,10)

So the part of the discrepancy is due to different rounding being used for decimal types than for float.

In accordance to the above rules it would be sufficient to use just one cast inside parenthesis. Every other literal will be promoted to float in accordance with Data Type Precedence rules.

1.0 / (1.0 / cast(60.0 as float))

And one more IMPORTANT thing. Even this float expression does not calculate exact result. It is just so that the front end (SSMS or whatever) rounds the value to (I guess) precision 6 digits and then truncates trailing zeros. So i.e. 1.000001 becomes 1.

Simple, isn't it?

SQL Server CAST Function By Practical Examples, In this tutorial, you will learn how to use the SQL Server CAST() function to In this statement, SQL Server implicitly converts the character string '1' to the number 1 . the lower data type to the higher one before it can process the calculation. expression can be a literal value or a valid expression of any type that will be  In addition, SQL Server can calculate SUM, COUNT, AVG, etc. For these type of calculations, check out SQL Server T-SQL Aggregate Functions. To address the multiple code issues, I would recommend researching stored procedures. This tip Getting started with SQL Server stored procedures is probably a good place to start.

To write a constant float expression, try to use scientific notation:

select (1.0E0 / (1.0E0 / 60.0E0))

The result is 60.

Exact Numeric vs Approximate Numeric, The numeric literals in the SQL statements are interpreted as approximate numeric For example, it is common to calculate the average of the data in an integer  This SQL Server tutorial explains how to use literals (strings, integers, decimals, and datetime values) in SQL Server with examples. In SQL Server, a literal is the same as a constant.

Numeric Literals, A numeric literal can be assigned to any of the numeric data types or the money required in SQL syntax (such as a list of table columns or SQL functions with  Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow. By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. Conversely, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs.

Numeric Literals, A number N with an exponent part X , is calculated as N * POW(10, X) . In some cases, adding zeroes at the end of a decimal number can increment the precision​  When you need to perform calculations in SQL statement, you use arithmetic expression. An arithmetic expression can contain column names, numeric numbers, and arithmetic operators. Read Prerequisites for this tutorial and practices if you haven't done so. Consider the following facts when using arithmetic operations in a SELECT statement:

Transact-SQL Programming: Covers Microsoft SQL Server 6.5 /7.0 and , Literals Transact - SQL judge literals as any explicit numeric , character , string , date , or Boolean value that is not an identifier . SQL Server allows a variety of  ALL instructs the AVG () function to take all values for calculation. ALL is used by default. DISTINCT instructs the AVG () function to operate only on unique values. expression is a valid expression that returns a numeric value. The AVG () function ignores NULL values.

Comments
  • Ah.. I didn't know that method until now. Thank you very much :)
  • So there's no way around an explicit cast to get the expected result? Is there another way to tell SQL Server that a given literal is of type (e.g.) float?
  • @VVS, besides an explicit cast as you already are doing, not that I know of. SQL Server always interpretes the value as numeric. The problem can be alleviated a bit if you yourself add precision to your values like 1.000000000000000000000000000000000000.
  • @Lieven: do you have any kind of documentation that confirms your statement that "SQL Server always interpretes the value as numeric"?
  • @VSS: I have added a reference to the documentation.
  • The rationale in the article is flawed. It is based on some arbitrary values. It would be easy to find counter example in favor of Decimal type. These Indian claims based on one empirical example are traps in itself.