Recently I discovered how SUM could change an output. Consider the following cases.

Scene 1

What will be output of this query:

CREATE TABLE dbo.CuriousCaseOfSum(val DECIMAL(18,0) NOT NULL)
INSERT INTO dbo.CuriousCaseOfSum(val)
 VALUES (1),(2),(3)
DECLARE @var DECIMAL(12,4)=12.6
SELECT SUM(val)+@var FROM dbo.CuriousCaseOfSum
DROP TABLE dbo.CuriousCaseOfSum

This query will output 19, which is the rounded off value of 1+2+3+12.6. This may not be a big deal as it looks like data type of CuriousCaseOfSum.Val column is decimal 18,0 which might have superseded decimal(12,4), the type of the variable.

Scene 2

OK, let’s increase the precision of the @var variable to the highest possible decimal range, i.e. the all mighty 38 (thunder storms in background).

CREATE TABLE dbo.CuriousCaseOfSum(val DECIMAL(18,0) NOT NULL)
INSERT INTO dbo.CuriousCaseOfSum(val)
 VALUES (1),(2),(3)
DECLARE @var DECIMAL(38,1)=12.5
SELECT @var+SUM(val) FROM dbo.CuriousCaseOfSum
DROP TABLE dbo.CuriousCaseOfSum

The output is still 19!!! Why does our super range fail?

Scene 3

Let’s break values inserted in table into variables.

DECLARE
  @a DECIMAL(18,0)=1,
  @b DECIMAL(18,0)=2,
  @c DECIMAL(18,0)=3,
  @var DECIMAL(36,1)=12.5;
SELECT @a+@b+@c+@var

Let’s run this query and see the output – 18.5.

Scene 4

Enter BOOKS ONLINE. The difference was the use of SUM. Lets see what BOL says about sum.

Return Types

Returns the summation of all expression values in the most precise expression data type.

If you notice the return type in the case of decimal is always 38 with whatever scale has been mentioned! Let’s connect the dots and solve the mystery.

In scenes 1 and 2, we were declaring a table with decimal(18,0). However, when we applied SUM over the column, the result changed the data type to decimal(38,0). Since it is decimal (38,0), the value will supersede the variable that was declared as decimal(12,4). In scene 3, since there wasn’t any sum function, data type remained same. That explains why we were getting values with a decimal.

Let's see an even simpler example:

DECLARE @x DECIMAL(38,0)=0
DECLARE @y DECIMAL(24,4)=4.2
SELECT @x-@y

This will output -4. Now let’s change @x type from 38 to 37.

DECLARE @x DECIMAL(37,0)=0
DECLARE @y DECIMAL(24,4)=4.2
SELECT @x-@y

The output is now -4.2!

When the precision is already at 38 and the scale is set to zero, there are no more bytes available for decimal places to be stored in the data type. Hence the result for any addition / subtraction will result in zero decimal places to the right to make sure the precision is accurate. However, when performing multiplication or division, this doesn’t apply because the decimal places and the scale matters. We cannot just truncate the result to fit the datatype of the result.

Conclusion

If you are having any numeric/decimal data type with precision 0, then SUM will return an integer, which can change expected result of query.