# How does SQL Server determine precision / scale?

I'm running SQL Server 2012

``````SELECT
0.15 * 30 / 360,
0.15 / 360 * 30
``````

Results:

`````` 0.012500,
0.012480
``````

This one is even mor confusing to me:

``````DECLARE @N INT = 360
DECLARE @I DECIMAL(38,26) = 0.15 * 30 / 360
DECLARE @C DECIMAL(38,26) = 1000000

SELECT @C *  @I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 )
SELECT @C * (@I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 ) )
``````

The first select gives me the correct result: 12644.44022 The second one truncates the result: 12644.00000

• See this answer: stackoverflow.com/a/51445919/87015, it contains a sample code that determines the expected data type based on input data type and operation. Jul 23 '18 at 8:31

Determining precision and scale resulting from expressions is a rat's nest and I don't think anyone understands the exact rules in every scenario, especially when mixing decimal (or float!) and int. See this answer by gbn.

You can of course tailor the expressions to give you what you want by making much more verbose explicit conversions. This is probably overkill but:

``````SELECT
CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15)
* CONVERT(DECIMAL(15,6), 30)
/ CONVERT(DECIMAL(15,6), 360)),
CONVERT(DECIMAL(15,6), CONVERT(DECIMAL(15,6), 0.15)
/ CONVERT(DECIMAL(15,6), 360)
* CONVERT(DECIMAL(15,6), 30));
``````

Neither result is rounded wrongly due to broken floating point math or wildly wrong precision/scale.

``````0.012500    0.012500
``````

As Aaron Bertrand mentioned, expressions are very tricky to predict.

If you dare go there, you could try to gain some insight using the following snippet:

``````DECLARE @number SQL_VARIANT
SELECT @number = 0.15 / 360
SELECT @number
SELECT
SQL_VARIANT_PROPERTY(@number, 'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@number, 'MaxLength') MaxLength,
SQL_VARIANT_PROPERTY(@number, 'Precision') Precision
``````

This is the result:

``````------------
0.000416

(1 row(s) affected)

BaseType     MaxLength    Precision
------------ ------------ ----------
numeric      5            6

(1 row(s) affected)
``````

Notwithstanding the excellent answers already added to this question, there is an explicitly defined order of precedence for conversion of data types in SQL Server.

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. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

SQL Server uses the following precedence order for data types:

``````user-defined data types (highest)
sql_variant
xml
datetimeoffset
datetime2
datetime
smalldatetime
date
time
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar (including nvarchar(max) )
nchar
varchar (including varchar(max) )
char
varbinary (including varbinary(max) )
binary (lowest)
``````

So, for instance, if you `SELECT 0.5 * 1` (multiplying a decimal by an int) you get a result that is converted to a decimal value, since `decimal` is higher precedence than the `int` data type.

See http://msdn.microsoft.com/en-us/library/ms190309.aspx for further details.

Having said all that, ```SELECT @C * (@I * POWER(1 + @I, @N) / (POWER(1 + @I, @N) - 1 )); ``` should probably return a decimal value, since practically all of the inputs are decimal. Interestingly, you can force a correct-ish result by modifying that `SELECT` to:

``````DECLARE @N INT = 360;
DECLARE @I DECIMAL(38,26) = 0.15 * 30 / 360;
DECLARE @C DECIMAL(38,26) = 1000000;

SELECT @C *  @I *  POWER(1 + @I, @N)  / (POWER(1 + @I, @N) - 1);
SELECT @C * (@I *  POWER(1 + @I, @N)  / (POWER(1E0 + @I, @N) - 1));
``````

This returns: I am at a loss to explain how that makes any difference, although clearly it does. My guess is the `1E0` (an explicit float) in the `POWER(` function forces SQL Server to make a different choice on output types for the `POWER` function. If my supposition is correct, that would indicate a possible bug in the `POWER` function, since the documentation states the first input to `POWER()` is a float, or a number that can be implicitly converted to a float.

Are you familiar with the `SELECT .. INTO` syntax? It's a useful trick for deconstructing situations like this because it creates a table on the fly with just the right data types for the given `SELECT` list.

You can break up your calculation into its constituent steps, applying SQL Servers' precedence rules as you go, to see how the definition changes. Here's how your first example would look:

``````use tempdb;

SELECT
0.15             as a,
0.15 * 30        as b,
0.15 * 30 / 360  as c
into #Step1;

select * from #Step1;

select
c.name,
t.name,
c.precision,
c.scale
from sys.columns as c
inner join sys.types as t
on t.system_type_id = c.system_type_id
where object_id = object_id('#Step1');

drop table #Step1;
``````

This is the output:

``````name    name        precision   scale
a       numeric     2           2
b       numeric     5           2
c       numeric     9           6
``````
• This doesn't always help though. Both `(1+1)` and `2` are of type `int` but this question has an example where they end up producing a differently typed result. Sep 26 '14 at 7:34

From my explanation of the full calculation:

One part of the answer is about data type precedence, one part is about operator precedence and the last part is about precision, scale, and length.

Let us start with operator precedence where we learn that multiplication is carried out before division. So for column (a) the multiplication of factor 0.15 and factor 30 is carried out first and the intermediate result is 4.5. Later, 4.5 is divided by 360 for the result of 0.012500.

For column (a) the factor 0.15 has a precision of 2 and scale of 2; (p ,s) equals (2, 2). This factor is then multiplied with factor 30, which SQL Server treats (p, s) as (2, 0) and not (10, 0) as expected for an INT, because of data type precedence described in the link above. The intermediate precision and scale is (5, 2) and the result of 0.15 * 30 is then 004.50. When SQL Server later divide by 360 (which is implicitly converted to a decimal with precision and scale of (3, 0) and not (10, 0) as expected for an INT), this gives the final (p, s) of (9, 6) and 4.50 divided by 360 is then 000.012500.

What happens with column (b)?

SQL Server first has to follow order of operators, which means the factor 0.15 / 360 must be calculated first. Dividend 0.15 has a (p, s) of (2, 2) and divisor 360 has a (p, s) of (3, 0), which gives the intermediate (p, s) of (6, 6) as described in the link above. The result of 0.15 / 360 is 0.00041666666666… which is truncated to fit in a (p, s) of (6, 6). Now we have the intermediate factor of 0.000416. This factor is then multiplied with 30 which has a (p, s) of (2, 0) for the final precision and scale of (9, 6), just as before! But 0.000416 multiplied with 30 is 000.012480.

And there is the full explanation of why the results differ in the SELECT query.