Applies to SQL Server 2019, 2022 and 2025.

The SQL Server bit data type holds 1, 0 or NULL, and most of us use it for flags: IsActive, IsDeleted, HasShipped. It’s classed as an integer type, so it’s natural to assume you can add bits together or total them. You mostly can’t, and the places where you think you can are where the surprises are.

What the SQL Server bit data type allows

bit sits in the integer category, but SQL Server treats it as a special case. The arithmetic operators (+, -, *, /, %) don’t accept bit as an operand, and neither do the aggregates SUM, AVG, MIN and MAX. COUNT works because it only counts rows, not values.

What bit does support is the bitwise operators: & (AND), | (OR), ^ (exclusive OR) and ~ (NOT). That makes sense once you think of a bit as a true/false value rather than a small number.

Two conversion rules are worth knowing too. Converting any nonzero number to bit gives 1, so 2, -1 and 0.5 all become 1. And the strings ‘TRUE’ and ‘FALSE’ convert to 1 and 0.

Adding bits: a worked example

Here’s a small table of orders with three flags. I want a count of how many checks each order has passed, and a count per flag across the table.

CREATE TABLE #OrderCheck
(
    OrderID      int NOT NULL PRIMARY KEY,
    IsPaid       bit NOT NULL,
    IsPicked     bit NOT NULL,
    IsDispatched bit NOT NULL
);

INSERT INTO #OrderCheck (OrderID, IsPaid, IsPicked, IsDispatched)
VALUES (1, 1, 1, 1),
       (2, 1, 1, 0),
       (3, 1, 0, 0),
       (4, 0, 0, 0);

-- This fails: bit isn't a valid operand for +
-- SELECT OrderID, IsPaid + IsPicked + IsDispatched FROM #OrderCheck;

-- Cast to a numeric type first, then add
SELECT OrderID,
       CAST(IsPaid AS tinyint)
     + CAST(IsPicked AS tinyint)
     + CAST(IsDispatched AS tinyint) AS ChecksPassed
FROM #OrderCheck;

-- SUM(IsPaid) fails too; cast inside the aggregate
SELECT SUM(CAST(IsPaid AS int))       AS PaidCount,
       SUM(CAST(IsPicked AS int))     AS PickedCount,
       SUM(CAST(IsDispatched AS int)) AS DispatchedCount,
       COUNT(*)                       AS OrderCount
FROM #OrderCheck;

-- If you want "any" or "all" rather than a count, use bitwise operators
SELECT OrderID,
       IsPaid | IsPicked | IsDispatched AS AnyCheck,
       IsPaid & IsPicked & IsDispatched AS AllChecks,
       ~IsDispatched                     AS NotDispatched
FROM #OrderCheck;

DROP TABLE #OrderCheck;

I cast to int inside SUM rather than tinyint, because the sum of a big table can easily pass 255. For adding three flags on one row, tinyint is plenty. The bitwise results stay as bit, which is exactly what you want for a yes/no column.

A COUNT with a CASE does the same job as the SUM if you prefer it: COUNT(CASE WHEN IsPaid = 1 THEN 1 END). I find the cast easier to read, but either is fine.

The common mistake

The mistake is using a bit to hold the result of adding bits, or a running total. It doesn’t fail; it quietly gives the wrong answer.

DECLARE @Passed bit = 0;

SET @Passed = @Passed + 1;  -- 1
SET @Passed = @Passed + 1;  -- still 1, not 2

SELECT @Passed AS PassedValue;

-- Same thing when you cast the total back to bit
SELECT CAST(CAST(1 AS int) + CAST(1 AS int) AS bit) AS TwoAsBit;  -- 1

When one side of the + is an int, data type precedence converts the bit to int, so the addition runs. The trouble comes when the answer goes back into a bit: any nonzero value becomes 1, so 2, 3 and 300 all come out as 1 and there’s no error or warning. You’ve written a counter that stops at one, and the code looks perfectly reasonable.

I’ve seen this in stored procedures that count failed checks into a bit variable, then report “1 failure” no matter how many there were. If the value is a count, give it a numeric type. Keep bit for things that really are yes or no.

Watch out for

• MIN and MAX don’t work on bit. If you want “is any row flagged”, use MAX(CAST(Flag AS int)) or an EXISTS test.
• NULL flags. A NULL bit cast to int is still NULL, so SUM skips it and adding it to other flags makes the whole row NULL. Wrap it in ISNULL if NULL should count as 0.
• Nonzero becomes 1. Loading a source column of 2 or -1 into a bit column gives 1 without any error. Check the source data if those values mean something.
• Don’t rely on implicit conversion. Mixing a bit with an int does work through precedence, but I always cast explicitly. It makes the intent obvious to the next person reading the code.
• Storage. SQL Server packs up to eight bit columns into one byte, so a lot of flags is cheap. That’s a reason to use bit for flags, not for counts.

Where it fits

bit is the right type for a flag, and the bitwise operators cover most of what you’d want to do with flags across a row. When you need numbers out of them, cast to int first and keep the result in a numeric type. There’s more on choosing data types and day-to-day T-SQL in my SQL Server DBA Handbook, and the T-SQL SIGN function post has another case where bit isn’t accepted as a number.

Microsoft’s reference page for bit (Transact-SQL) lists what the type supports, and the bitwise operators page covers &, | and ^.

This topic came up in the SQLServerCentral Question of the Day: Adding Bits I.

Clive Roberts DBA