If i use Aggregate Function MIN() on BIT data type column then i got below error message.
"Operand data type bit is invalid for min operator."
I am clear about how to avoid the error, but i would like to know
why bit data type columns are not allowed in MIN Aggregate function? Is there any reason behind that?
@gbritton, yep, I thought of that after posting @khtan, if the MIN worked the NULL would not be considered, but it is good information as @mmkrishna1919 did not indicate what they were trying for
Thanks all for your replies.
@@djj55: I was going through MSDN library, i came across this statement. But i didn't find any technical reason behind this restriction.
There is a technical reason, what is the sum of 1 + 1? Well, 2, obviously! However, 2 cannot fit in a bit! The sum would take on the type of the column being summed, unless you write
sum(cast mybit as int)
Also a theoretical reason; Since bits are used to represent boolean values, it makes no sense to do normal arithmetic on them. e.g. what is True plus False?
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.
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....