ABS: Widen an Integer Before Taking Its Magnitude

ABS can overflow even when the negative input fits its signed integer type. I widen the input before asking for its magnitude. Casting the output afterward would be too late to protect the calculation.

A signed integer range contains one additional negative value. Its smallest value therefore has no matching positive value in the same type. That boundary is easy to miss when ordinary small examples all succeed.

Put the widening inside the expression

The first query uses int inputs, including the minimum int value. Each input is cast to bigint inside ABS. The returned magnitude then has the larger signed range available during calculation.

The query also includes negative seven, zero, positive seven, the largest int and NULL. Those rows make the ordinary behavior visible beside the boundary case. A type diagnostic identifies the nonmissing result as bigint.

The second query handles the minimum bigint differently. It casts that value to decimal before calling ABS. Moving from int to bigint solves the int boundary, but does not solve every wider boundary automatically.

WITH Inputs AS
(
    SELECT CaseId, InputValue
    FROM (VALUES (1, CAST(-2147483648 AS int)), (2, -7),
                 (3, 0), (4, 7), (5, 2147483647), (6, NULL)) AS v(CaseId, InputValue)
)
SELECT CaseId, InputValue, ABS(CAST(InputValue AS bigint)) AS WidenedMagnitude,
       CAST(SQL_VARIANT_PROPERTY(ABS(CAST(InputValue AS bigint)), 'BaseType') AS varchar(20)) AS MagnitudeType
FROM Inputs
ORDER BY CaseId;

SELECT CAST(-9223372036854775808 AS bigint) AS MinimumBigint,
       ABS(CAST(CAST(-9223372036854775808 AS bigint) AS decimal(20,0))) AS DecimalMagnitude,
       CAST(SQL_VARIANT_PROPERTY(ABS(CAST(CAST(-9223372036854775808 AS bigint) AS decimal(20,0))), 'BaseType') AS varchar(20)) AS MagnitudeType;

Read the expected magnitudes

The minimum int is negative 2147483648. Its expected widened magnitude is positive 2147483648, which fits bigint. That value is one greater than the maximum positive int.

Negative seven becomes seven, while zero remains zero. Positive seven and the maximum int keep their magnitudes. The NULL input remains NULL, including the diagnostic for a missing SQL variant value.

The minimum bigint has magnitude 9223372036854775808. It exceeds the positive bigint limit by one. The decimal input in the second query can represent that magnitude exactly.

Its value remains exact, and its base type is decimal rather than bigint.

An outer cast does not fix an inner overflow

The order of operations matters. In an expression that casts ABS of an int afterward, ABS still receives the original int. Its overflow can occur before the outer conversion is reached.

The safe expression casts the argument instead. That changes the type on which ABS operates. The batch above uses this form and does not deliberately execute the overflowing int expression.

The minimum int overflows with an arithmetic overflow error. That explains the boundary without requiring an error-producing demonstration in every reader’s session. The safe query gives a complete alternative with observable type information.

Do not replace exact whole-number arithmetic with float merely to obtain a wider magnitude. Float has a different precision contract. Choose a type that fits both the range and the exactness requirement.

Apply the same range reasoning elsewhere

A larger input type still has its own limits. The bigint example is included to make that point concrete. Check the full range before deciding that one widening step is always sufficient.

The expression only calculates magnitude. It does not explain whether a negative source amount represents debt, correction or direction. Keep the original signed value beside the magnitude when that meaning matters.

This read-only example does not compare execution plans or benchmark types. It isolates arithmetic behavior with bounded constants. A real calculation involving subtraction may need widening before the subtraction too.

When adapting it, inspect the entire arithmetic expression rather than only the final function call. An earlier intermediate result can overflow independently. The useful rule is to provide enough range before the operation that needs it.

Give your own numbers a bit more room and the overflow never shows up.

ABS is not always a safe magnitude, it is an operation that can overflow its own type.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.