sqlfingers.com: Cursed SQL, Part Three: Six Numbers That Lie

Two rounds of Cursed SQL so far. First came six queries that run fine until they don't, then six ways NULL lies to your face. Here is round three, and this time the villain is arithmetic.

Every example below either quietly returns the wrong answer or explodes because the answer won't fit. SQL Server did exactly what its rules say. Same curse as always, but the danger is not always an error. Sometimes it is a wrong answer delivered with a straight face. Paste each into a query window and watch.

Curse 1: Integer Division Floors Your Percentage to Zero

You want the percent complete. Completed over total, times one hundred. It could not be simpler.

DECLARE @Completed int = 30, @Total int = 100;
SELECT @Completed / @Total * 100 AS PctComplete;  -- 0

Thirty out of a hundred. You expect 30 but you get 0.

huh? Both operands are integers, so SQL Server does integer division: 30 / 100 is not 0.3, it is 0, because the fractional part gets thrown away before anything else happens. End result, 0 * 100 = 0. The multiplier never had a chance.

The fix is to force one operand to a decimal before the division, so the division itself is done in decimal math:

SELECT @Completed * 100.0 / @Total AS PctComplete;   -- 30.000000

The order matters here. @Completed / @Total * 100.0 still gives you 0.0, because the integer division happens first and hands a 0 to the multiply. You need to promote the type early -- multiply by 100.0 up front, or cast a column before you divide it. The behavior follows straight from the division operator's type rules: int over int yields int.

Run them yourself, you'll see.

Curse 2: @@IDENTITY Hands You the Trigger's Key, Not Yours

This one doesn't just return a wrong number, it writes one into your data. You insert an order, grab the new identity, and use it as a foreign key value. Standard stuff. Except there is an audit trigger on the table that nobody told you about.

CREATE TABLE dbo.Orders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    Amount MONEY NOT NULL
);

CREATE TABLE dbo.OrderAudit
(
    AuditID INT IDENTITY(1000,1) PRIMARY KEY,   -- starts at 1000 on purpose
    OrderID INT NULL,
    LoggedAt DATETIME DEFAULT SYSDATETIME()
);
GO

CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders AFTER INSERT
AS
BEGIN
    INSERT dbo.OrderAudit (OrderID) SELECT OrderID FROM inserted;
END
GO

INSERT dbo.Orders (Amount) VALUES (49.99);
SELECT @@IDENTITY AS Identity_Returned,    -- the AUDIT row
       SCOPE_IDENTITY() AS Scope_Returned; -- the ORDER row

Your new OrderID is 1. But look what the two functions hand back:

@@IDENTITY returns 1000 as the AuditID of the row the trigger inserted because @@IDENTITY reports the last identity generated anywhere in your session, regardless of scope. The trigger fired inside your session, inserted last, and won. Write that into your FK value and you've just pointed a child row at an order that does not exist. Use SCOPE_IDENTITY() to stay inside your scope and return the value that you intended, like this:

SELECT * FROM dbo.Orders; -- see what's in the table

DECLARE @NewOrderID int;
INSERT dbo.Orders (Amount) VALUES (49.99);
SET @NewOrderID = SCOPE_IDENTITY();   -- correct for this single-row insert
SELECT @NewOrderID AS NewOrderID;     -- 2  (the order you just inserted)

SELECT * FROM dbo.Orders; -- see what's in the table

DBA Bonus

SCOPE_IDENTITY() is the correct fix here because this example inserts a single row. But if you're writing new code that inserts multiple rows, the OUTPUT clause is an even better pattern because it captures every identity value generated by the statement.

DECLARE @NewOrders TABLE
(
    OrderID int
);

INSERT dbo.Orders (Amount)
OUTPUT INSERTED.OrderID
INTO @NewOrders (OrderID)
VALUES (49.99);

SELECT *
FROM @NewOrders;

Curse 3: Money in FLOAT Drifts

You stored amounts in FLOAT because it was there and it held decimals. Then the report got flagged a penny short and you couldn't find that penny anywhere. Here it is:

SELECT CASE WHEN CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) = CAST(0.3 AS FLOAT)
            THEN 'equal' ELSE 'NOT equal' END AS FloatCheck;  -- NOT equal

NOT equal? Technically, one tenth plus two tenths is not three tenths -- as you can see here:

SELECT CAST(CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) AS DECIMAL(19,17)) AS FloatSum;

FLOAT is an approximate numeric type. Values like 0.1 and 0.2 have no exact representation in binary, so their sum lands a hair off of 0.3. Harmless when you are measuring distances to a star, but fatal when you're dealing with finance. Run that across real orders and those hairs will build into real cents. A SUM of FLOAT amounts does not match the same data summed as DECIMAL. Financial data belongs in DECIMAL, where values are represented exactly rather than approximately.

SELECT CASE WHEN CAST(0.1 AS DECIMAL(4,2)) + CAST(0.2 AS DECIMAL(4,2)) = CAST(0.3 AS DECIMAL(4,2))
     THEN 'equal' ELSE 'NOT equal' END AS DecimalCheck;   -- equal

Curse 4: CONVERT to INT Truncates -- and the Money Disappears

Same value, same CONVERT -- but the data type you use may either round or just lose cash. Convert an amount to INT and SQL Server truncates it; convert that very same amount to DECIMAL and it rounds. Watch one line item of $2.99:

SELECT CONVERT(INT, 2.99) AS AsINT,             -- 2  the 0.99 is chopped off
       CONVERT(DECIMAL(19,0), 2.99) AS AsDEC,   -- 3  rounded to the nearest dollar
       CONVERT(DECIMAL(19,2), 2.99) AS AsMoney; -- finance won't be coming after you now

Look at those top two. One value, two answers, and neither is $2.99. Converting to INT drops everything after the decimal point. It does not round. It just chops, so the ninety-nine cents is just gone. Converting to a zero-scale DECIMAL rounds, which is a bit tidier, but it still throws the cents away. Just in the other direction.

The safer method is to store financial values in a DECIMAL with the necessary precision and scale, so every penny survives and every calculation returns accurately. Avoid converting money to whole-number types unless losing the cents is truly the point.

Curse 5: SUM Overflows the Column It Started In

A total that runs clean for years -- right up until the numbers get bigger. SUM over an INT column returns an INT, and the moment the running total crosses INT's ceiling, we've got a problem.

SELECT SUM(Amount) AS Total
FROM (VALUES (CAST(2000000000 AS INT)),
             (CAST(2000000000 AS INT))) AS t(Amount);

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Each value fits into an INT fine, but their sum, four billion, does not -- it exceeds INT's ceiling of 2,147,483,647, and the SUM overflows rather than widening to hold the result. CAST to BIGINT before you sum, and the accumulator has room:

SELECT SUM(CAST(Amount AS bigint)) AS Total
FROM (VALUES (CAST(2000000000 AS int)),
             (CAST(2000000000 AS int))) AS t(Amount);   -- 4000000000

Curse 6: DATEDIFF Overflows at Scale

This is the merciful curse that actually throws an error instead of silently lying to you. Measuring the seconds between two timestamps separated by years is real work: high-frequency analytics, scientific modeling, precise duration tracking all depend on it. The expression looks harmless and runs fine for years, until someone hands it a wide enough range:

SELECT DATEDIFF(SECOND, '2000-01-01', '2100-01-01') AS SecondsApart;

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating
two date/time instances is too large. Try to use datediff with a less precise datepart.

DATEDIFF returns an INT. A century in seconds is about 3.15 billion -- well past INT's ceiling of 2,147,483,647 -- so it overflows. It runs clean across small ranges and detonates the day a report spans too many days. Intentionally or by fat finger, it's going to happen. The fix is DATEDIFF_BIG, which returns a BIGINT (SQL Server 2016 and later), or a coarser datepart if you do not need second-level precision:

SELECT DATEDIFF_BIG(SECOND, '2000-01-01', '2100-01-01') AS SecondsApart; -- 3155760000

In Summary

Six shapes, one lesson: SQL Server does the math you tell it to, but not always the math that you actually meant. Integer division throws away the fraction before you ever multiply. @@IDENTITY answers for the whole session, not just your statement. FLOAT only approximates. Converting money to int chops the cents instead of rounding them. SUM keeps its total until it overflows, and DATEDIFF quietly runs on an INT until the range is too large -- and it overflows as well.

None of these are bugs. They're all consequences of SQL Server's rules, and every one has a small, deliberate fix. Promote the type before you divide, reach for SCOPE_IDENTITY(), store money in DECIMAL(p,s) for financial calculations, and use DATEDIFF_BIG when the span is wide. Spot the shape and the curse lifts. Now you have six more to watch for.

Cleanup

DROP TABLE dbo.Orders;        -- the audit trigger drops with it
DROP TABLE dbo.OrderAudit;

SQL Server Consulting

Monday, June 29, 2026

Cursed SQL, Part Three: Six Numbers That Lie