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).
- If I were to provide an integer to either one in a query, would there be a difference?
- If not, which is better to use?
- Is one faster?
- Do they have differing methods for rounding?
I am using these in both query of queries (QoQ) as well as in MS-SQL queries.
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:DECIMAL
columns useCF_SQL_DECIMAL
NUMERIC
columns useCF_SQL_NUMERIC
Usage is pretty straight-forward. Just supply the
value
and desiredscale
:Notes about scale:
scale
defaults to zero (0). Meaning all decimal places are dropped.scale
must be an integer greater than zero, and obviously should not exceed theprecision
(total number of digits stored) of the target columnFrom CF's perspective, they are essentially identical. CFQueryparam simply validates the input (numeric, within range, etcetera), and converts the
value
to ajava.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.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
andnumeric
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).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.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