Nuances between CF_SQL_DECIMAL and CF_SQL_NUMERIC

Question

What are the nuances between CF_SQL_DECIMAL and CF_SQL_NUMERIC? It sounds like they both accept an argument called scale (per http://csis.pace.edu/~ctappert/cs615-02/support/Help/CFML_Language_Reference/lang_0277.htm).

1. If I were to provide an integer to either one in a query, would there be a difference?
   1. If not, which is better to use?
2. Is one faster?
3. Do they have differing methods for rounding?

I am using these in both query of queries (QoQ) as well as in MS-SQL queries.

Answer 1

If you are wondering which cfsqltype to use, it is determined by the data type of the target column. The link you posted is from a very old version of the documentation. Newer ones include a matrix for most major databases. In the case of SQL Server, it lists the following mappings:

• For DECIMAL columns use CF_SQL_DECIMAL
• For NUMERIC columns use CF_SQL_NUMERIC

Usage is pretty straight-forward. Just supply the value and desired scale:

    <cfqueryparam value="#someNumber#" scale="2" cfsqltype="cf_sql_decimal"> 
    ... OR ....
    <cfqueryparam value="#someNumber#" scale="2" cfsqltype="cf_sql_numeric"> 

Notes about scale:

• If omitted, the scale defaults to zero (0). Meaning all decimal places are dropped.
• When specified, scale must be an integer greater than zero, and obviously should not exceed the precision (total number of digits stored) of the target column

What are the nuances between CF_SQL_DECIMAL and CF_SQL_NUMERIC?

From CF's perspective, they are essentially identical. CFQueryparam simply validates the input (numeric, within range, etcetera), and converts the value to a java.math.BigDecimal object. Finally it sends the SQL off to the database for execution. That is the extent of CF's involvement. The rest is handled by the database. So any behavioral nuances are likely to be database/driver specific.

If I were to provide an integer to either one in a query, would there be a difference? Do they have differing methods for rounding?

I do not think there is a single answer across the board. Overall, most databases have similar handling of decimal/numeric data types. However, the exact details may vary depending on your database/driver. So you need to review your database's documentation.

SQL Server's documentation says "the decimal and numeric types are exactly equivalent to each other. (Though elsewhere they make the distinction of saying "functionally" equivalent). So they should behave the same way. My take on it is if one rounds, the other one will too, same with overflows. For more details, see: decimal and numeric (Transact-SQL).

• By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.

• Converting from int, smallint, ... to either decimal or numeric can cause overflow.

While you probably could use either cfsqltype in this case, again it is best to follow the API. Use the cfsqltype which maps to the data type of the target column.

in both query of queries (QoQ) as well as in MS-SQL queries.

QoQ's are a totally different beast than database queries. They are also known to be a bit ... quirky at times. Your best bet is to check the documentation: Query of Queries user guide