Avoid arithmetic overflow on aggregate function

Question

Is there a way (aside from casting i to something like a bigint) to avoid the arithmetic overflow error in the following set of statements?

DECLARE @tbl TABLE ( [i] INT );

INSERT  INTO @tbl
        ( [i] )
VALUES  ( POWER(2, 30) ),
        ( POWER(2, 30) );

SELECT  SUM([t].[i])
FROM    @tbl AS [t];

I tried a TRY_CAST() on the SUM() but it seems that the error is happening before that point. I'm fine if a NULL (or any other value) gets returned on an overflow.

Martin Smith · Accepted Answer · 2016-01-29 22:05:39Z

3

Casting to bigint before the aggregate then try_casting the result back to int would be preferable to me

But a way without that is

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

  DECLARE @tbl TABLE ( [i] INT );

    INSERT  INTO @tbl
            ( [i] )
    VALUES  ( POWER(2, 30) ),
            ( POWER(2, 30) );

    SELECT  SUM([t].[i])
    FROM    @tbl AS [t];

SQL Fiddle

edited Jan 29, 2016 at 22:05

answered Jan 29, 2016 at 21:59

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TT. Over a year ago

It is important to note that Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues. You should really be looking for another way to avoid arithmetic overflow.

Martin Smith Over a year ago

@TT. it prevents indexed views and indexes on computed columns being matched but it doesn't have any other negative effects on query optimisation if those aren't being used. The warning in the article is about having mismatched values for this setting making troubleshooting more difficult, as different sessions with different values for these options can't share each other's plans so it is possible to have a parameter sniffing issue that only happens to show up in the plan used by the application then running in SSMS all works fine.

TT. Over a year ago

@MartinSmith You wouldn't happen to know of an official link for that, would you? I'm always interested in knowing about such thing.

Collectives™ on Stack Overflow

Avoid arithmetic overflow on aggregate function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related