I know that when SQL Server handles an implicit conversion between types it converts the lowest priority one to the highest one. But what is the result datatype when I perform an operation between decimals with different precisions?
Paul White (imported from SE)
The type of the result is determined by the rules set out in [Precision, scale, and Length (Transact-SQL)]: >The following table defines how the precision and scale of the result are calculated when the result of an operation is of type **decimal**. The result is **decimal** when either: > >* Both expressions are **decimal**. >* One expression is **decimal** and the other is a data type with a lower precedence than **decimal**. > >The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not **decimal** is the precision and scale defined for the data type of the expression. The function max(a,b) means the following: take the greater value of "a" or "b". Similarly, min(a,b) indicates to take the smaller value of "a" or "b". > >[![table]] > >\* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result. In some cases such as multiplication or division, scale factor won't be reduced, to maintain decimal precision, although the overflow error can be raised. > >In addition and subtraction operations, we need `max(p1 - s1, p2 - s2)` places to store integral part of the decimal number. If there isn't enough space to store them that is, `max(p1 - s1, p2 - s2) < min(38, precision) - scale`, the scale is reduced to provide enough space for integral part. Resulting scale is `MIN(precision, 38) - max(p1 - s1, p2 - s2)`, so the fractional part might be rounded to fit into the resulting scale. A convenient quick way to see the resulting type is to use [`SQL_VARIANT_PROPERTY`]: ``` DECLARE @V1 decimal (9, 6) = 123.456789; DECLARE @V2 decimal (7, 2) = 12345.67; SELECT [BaseType] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'BaseType'), [Precision] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'Precision'), [Scale] = SQL_VARIANT_PROPERTY(@V1 + @V2, 'Scale'); ``` Output: ```none ╔══════════╦═══════════╦═══════╗ ║ BaseType ║ Precision ║ Scale ║ ╠══════════╬═══════════╬═══════╣ ║ decimal ║ 12 ║ 6 ║ ╚══════════╩═══════════╩═══════╝ ``` Demo: <>https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=94c5a290b7b882253e713f8752771cde : https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql : https://i.stack.imgur.com/YKdYG.png : https://docs.microsoft.com/en-us/sql/t-sql/functions/sql-variant-property-transact-sql : https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=94c5a290b7b882253e713f8752771cde