Why does SUM(real) return a float datatype in SQL Server?

sql-server, sqldatatypes

Solution

`real` is just a synonym for `float(24)`. Ref: float and real (Transact-SQL)

When you SUM multiple `real`s, even 2 of them, you could overflow a `real` datatype, so it gets promoted to a larger datatype.

Test:

declare @t table (a real);
insert @t select 123;
select SQL_VARIANT_PROPERTY(sum(a), 'basetype'),
       SQL_VARIANT_PROPERTY(sum(a), 'precision'),
       SQL_VARIANT_PROPERTY(sum(a), 'scale')
from @t;

-------------
float    53    0

This is the same as certain string functions causing the result to go to (N)Varchar(MAX), which is common when used in Common Table Expressions, requiring an explicit CAST to get rid of expression type inequality errors.

Problem

If I have a table like this: Items ``` Name varchar(50) Value real ``` Then I create a view: ``` select sum(Value) as ValueSum from Items ``` The ValueSum datatype comes out to be a float. Why doesn't it remain a real datatype? Also, has this changed between SQL Server 2005 and 2008? I have an old database backup where ValueSum in a view returns as a real not float. I can restore the backup and if I look at the view it looks like it is returning a real, but if resave it becomes a float.

Original source