Applies to SQL Server 2019, 2022 and 2025.

The T-SQL SIGN function tells you which side of zero a number sits on. It returns 1 for a positive value, 0 for zero and -1 for a negative value. It’s one of those small functions most people forget exists until they need to split a column of amounts into money in and money out, and then it saves a fair bit of CASE typing.

How the T-SQL SIGN function works

SIGN takes one numeric argument: any exact or approximate numeric type except bit. There are only three possible answers, plus NULL if you pass it NULL. What surprises people is the data type of the answer. It isn’t always an int; it follows the type you passed in:

• bigint gives bigint
• int, smallint and tinyint give int
• money and smallmoney give money
• decimal and numeric give decimal/numeric
• float and real give float

So SIGN(-42) returns -1, but SIGN(CAST(-42.50 AS decimal(10,2))) returns a decimal, and SIGN(CAST(-1 AS real)) comes back as a float. The value is the same; the type isn’t, and that matters if you concatenate it into a string or feed it into something that expects a whole number.

A working example

Here is a small ledger with a mix of credits, debits, a zero adjustment and a row nobody has filled in yet.

CREATE TABLE #Ledger
(
    EntryId int IDENTITY(1,1) PRIMARY KEY,
    Amount  decimal(10,2) NULL
);

INSERT INTO #Ledger (Amount)
VALUES (250.00), (-75.50), (0.00), (1200.00), (-19.99), (NULL);

-- One row per direction
SELECT SIGN(Amount)  AS Direction,
       COUNT(*)      AS Entries,
       SUM(Amount)   AS Total
FROM #Ledger
GROUP BY SIGN(Amount)
ORDER BY Direction;

-- A readable label, using SIGN as an index into CHOOSE
SELECT EntryId,
       Amount,
       CHOOSE(CAST(SIGN(Amount) AS int) + 2, 'Out', 'Zero', 'In') AS Flow
FROM #Ledger;

The first query gives four groups: NULL, -1, 0 and 1. The NULL row doesn’t vanish and it isn’t counted as zero; it gets a group of its own. The second query adds 2 to the sign so that -1, 0 and 1 become positions 1, 2 and 3 in the list, and the NULL row comes out with a NULL label. I’ve cast to int there to make the intent obvious, since SIGN of a decimal returns a decimal.

SIGN is also handy for “move towards zero” logic. SIGN(x) * ABS(x) gives you x back, and SIGN(x) * FLOOR(ABS(x)) truncates towards zero rather than downwards, which is what people usually mean when they say “drop the decimals” on a negative number.

The common mistake: using SIGN to filter

Because SIGN reads nicely, I see it turn up in WHERE clauses against real ledger tables:

-- Works, but wraps the column in a function
SELECT EntryId, Amount
FROM dbo.Ledger
WHERE SIGN(Amount) = -1;

-- Same rows, and an index on Amount can be used for a seek
SELECT EntryId, Amount
FROM dbo.Ledger
WHERE Amount < 0;

Both return the same rows. The difference is that the first one puts the column inside a function, so the optimiser can’t seek on an index on Amount; it has to read every row and work out the sign for each one. On a few thousand rows you won’t notice. On a big transaction table it’s a scan where you could have had a seek. Keep SIGN for the SELECT list and GROUP BY, and filter with plain comparisons.

The second part of the same mistake is forgetting NULL. WHERE SIGN(Amount) <> 1 looks like “everything that isn’t positive”, but it skips the NULL rows, because comparing NULL to anything is unknown. If NULLs mean something in your data, ask for them explicitly with OR Amount IS NULL.

Watch out for

• The return type follows the input. Don’t assume an int; cast it if you need one.
• NULL in, NULL out. SIGN never turns a missing value into 0.
• bit isn’t allowed. SIGN on a bit column raises an error; it isn’t treated as 0 or 1.
• ABS has an edge case. ABS(CAST(-2147483648 AS int)) fails with an arithmetic overflow, because +2,147,483,648 doesn’t fit in an int. The same goes for the smallest bigint and smallint values (tinyint has no negatives). Cast to a wider type first if you can hit the minimum value.
• Tiny isn’t zero. A float result such as 1E-300 is still positive, so SIGN returns 1. If you mean “close enough to zero”, ROUND the value first.

Microsoft’s reference for SIGN (Transact-SQL) lists the return types in full, and the mathematical functions page covers ABS, FLOOR and the rest. If you liked the CHOOSE trick above, my post on the T-SQL CHOOSE function goes into it properly, and more of my notes are in the SQL Server DBA Handbook.

This topic came up in the SQLServerCentral Question of the Day: Negative and Positive Numbers.

Clive Roberts DBA