Sql Server SUM function with Int and Decimal Conversion

cpjpxq1n 于 2022-12-22 发布在 SQL Server

关注(0)|答案(5)|浏览(206)

Recently I was Playing with SQL Server data types, and a Large number of data in the table and trying to figure out the Performance with Varchar and Numeric data. But, I got some error which I don't think should not have been but it is. My problem is below :
I have a table :

create table sa(f1 varchar(100))

I have a Stored Procedure that Inserts 100000 data into the table :

create proc sad
as
begin
    declare @i int=0
    while @i<100000
    begin
       insert into sa values(@i)
       set @i=@i+1
    end
end

exec sad

And I have tested the following:

select CONVERT(int,f1) from sa  //Works Fine, i tested after the Problem
select sum(convert(int,f1)) from sa  //Didn't Worked, So i tested above and Below
select sum(convert(decimal(18,2),f1)) from sa //And, it again works fine

But, When I sum Converting F1 to Int, it shows me an error.
But, when I only select Converting to Int it's fine.
And, when I sum Converting F1 to decimal it works Fine.
What is the SUM function data type?
On the Above data, it works well with Decimal but not Int?
Why?
I'm Getting the Following error

Arithmetic overflow error converting expression to data type int.

sql-server

来源：https://stackoverflow.com/questions/17946902/sql-server-sum-function-with-int-and-decimal-conversion

5条答案

按热度按时间

92dk7w1h1#

You're summing as INT which has a range that cannot hold that sum.
The DECIMAL can.
The sum of all values from 1 up to 99999 is 4999950000, the maximum INT value is 2147483647, less than half of what the sum ends up as.
When you sum INT's, you're getting a new INT. When you're summing DECIMAL's, you're getting a new DECIMAL, so the input type defines the output type.
You can switch to using bigint instead, and it should "be fine".
Also, on a second note, please don't store numbers as text!

赞(0）回复(0）举报 2022-12-22

vohkndzv2#

According to MS documentation (see https://learn.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql ), the SUM() function returns values of a different type, according to the datatype of the column you are adding: if the column is of type int , tinyint , or smallint , then SUM returns values of type int .
Converting to bigint or decimal makes SUM() return a larger datatype, this explains why in that case you have no overflow.

赞(0）回复(0）举报 2022-12-22

w8f9ii693#

Expect you have exceeded the maximum int value that SQL Server allows (2,147,483,647) - see https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql .
Decimal allows a far higher limit of up to 10^38 - 1 (i.e. 1 with 38 zeros after) - see https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql .
However, if the values are of type int , I wouldn't recommend converting to decimal . Decimal values are useful when you have figures with possible numbers after the decimal place with a known precision and scale (e.g. for currency, percentages etc.) As another poster has suggested, the best conversion here would be to a bigint :