MIN aggregate function to a BIT field

True, but that wouldn't necessarily prevent MIN/MAX. Still a needless restriction in the name of pedantry; I even suspect at some point it might be lifted and you'll be able to use MIN/MAX at least.

You must have a more suspicious nature than I do :smile:

Probably :smile:. I prefer practicality to win out over pedantry, even if/when it doesn't happen.

An integer data type that can take a value of 1, 0, or NULL.

I hate to be completely pedantic but...

(Have you noticed that when people say "I hate to..." they really mean "I'm about to...")

From BOL: Bit Data-type "An integer data type that can take a value of 1, 0, or NULL." If it's an integer it should share the same set of operations allowable to other integer. Yes, SUM would have a ridiculously low overflow value but MIN and MAX would definitely have useful applications.

Now, if you'll excuse me, I have to go back to making sure that all of my papers are at true right angles on my desk....

select sum(cast(MyBit as integer)) & 1 as XOR from MyTable

Shows the problem nicely. BOL says its an integer. SQL does not treat a bit like an integer. You have to cast it (as you did) or get

Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for max operator.

Well... What is minimum of a TRUE or FALSE value?
It just happens that Microsoft has chosen 1 for TRUE och 0 for FALSE.

Use MIN(CASE WHEN BitColumn = 1 THEN 1 ELSE 0 END)
Or MAX(CASE WHEN BitColumn = 1 THEN 1 ELSE 0 END)

It seems to me that MIN would cause a scan, anyway. Wouldn't it be simpler and faster to use EXISTS in this case?

     IF EXISTS (SELECT * FROM dbo.YourTable WHERE SomeBitColumn = 0)
        SELECT 0
   ELSE SELECT 1
;

This does assume no nulls...

Heh... I think that anyone that would allow NULLs in a BIT datatype has bigger problems.

I think you could have situations where a bit value is unknown, and you're unsure yet whether the bit should be on on or off.

That would raise the same kind of non-SARGable hell as having NULL EndDates in a table, IMHO. I suppose there could be a reason to have "unknown" conditions for a bit but I can't think of one requiring such tri-state logic in a properly designed database.

Shifting gears to the public to ask the general question... If anyone has a good idea of when tri-state bits (nullable bits) would be appropriate (as in good design). I'd sure like to hear what it is.

Simple example:

You have a database for an obstetrician. You have a patient table has a bit for sex of the baby: 0 = Male, 1 = Female, null = unknown (too early in the pregnancy to tell)

Note that this is extremely common in type 2 SCDs

A NULL EndDate wouldn't necessarily make the WHERE condition nonsargable, unless one made the egregious error of using "ISNULL()" in the WHERE, which should never be done anyway.

I can't post an in-line tally table, so I had to use a physical one, but it still demonstrates the "seekability" of "OR IS NULL":

INSERT INTO #t1 ( start_date, end_date, description )
SELECT DATEADD(SECOND, -tally * 5, GETDATE()) AS start_date,
    CASE WHEN tally % 10 = 0 THEN NULL ELSE DATEADD(SECOND, -tally * 5 - 5, GETDATE()) END AS end_date,
    'Tally value was ' + CAST(tally AS varchar(10)) AS description
FROM dbo.tally
WHERE tally BETWEEN 1 AND 1000000

SELECT COUNT(*) FROM #t1

SET STATISTICS IO ON

SELECT COUNT(*)
FROM #t1

--as expected, this is a nice index seek
SELECT COUNT(*)
FROM #t1
WHERE start_date >= DATEADD(MINUTE, -10, GETDATE()) AND
    (end_date <= DATEADD(MINUTE, -9, GETDATE()))

--this is also an index seek
SELECT COUNT(*)
FROM #t1
WHERE start_date >= DATEADD(MINUTE, -10, GETDATE()) AND
    (end_date <= DATEADD(MINUTE, -9, GETDATE()) OR end_date IS NULL)

SET STATISTICS IO OFF

DROP TABLE #t1

I say it should still not be nullable and it certainly should not be the bit data type. There is such a thing as a hermaphrodite and the default should be something other than a NULL for "Not yet tested".

You're not wrong there. That's precisely one of the uses of NULLs that I try to avoid because of the NULL logic required when you want to know if something will either expire in the future or has no expiration date. Yes, I do agree that the required NULL logic isn't difficult but it's still extra logic.

No problem. What are you using for an index on #T1?

Granted, however it is pretty easy to cook up other simple examples. Schrodinger's cat. e.g. it's condition is unknown (null) until we open the box. After that it's definitely alive or dead. (not aware of other possibilities!)

Sorry for not showing that. It's ( start_date, end_date )