CODEWITHSUNDEEP
sql-serversqldatatypes
dtc
dtc

Reputation: 10296

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

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.

Upvotes: 3

Views: 2453

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107746

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

When you SUM multiple reals, 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.

Upvotes: 4

Related Questions