Booleans in SQL Server

Does your database already exist? What is the datatype of the myBoolean column? The reason I ask is because SQL Server does not support a boolean datatype. The closest it comes is the BIT datatype, although most people use TINYINT, SMALLINT, or INTEGER instead, because these integer datatypes offer more flexibility when constructing queries.

Consider the following SQL, which creates and populates a table with a BIT column:

create table test_bit
( id    tinyint not null identity
, yesno bit     not null )


insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (0)
insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (0)


select * from test_bit


id   yesno 
---- ----- 
1    1
2    1
3    0
4    1
5    0

So far, so good, right? Okay, now try a query:

select sum(yesno)
  from test_bit


Server: Msg 409, Level 16, State 2, Line 1

The sum or average aggregate operation 
cannot take a bit data type as an argument.

Well, that sort of makes sense, if you accept that BIT is not actually a numeric datatype, even though you must specify values for it using 1 and 0.

Try another query:

select yesno, count(*)
  from test_bit
group 
    by yesno 


Server: Msg 252, Level 16, State 2, Line 1
Cannot group by a bit column.

That makes no sense to me. That is, I understand what the error means, but not why SQL Server won't let you do it.

You can get around these limitations by using CAST(yesno as INTEGER) instead. But then why not define the column using INTEGER to begin with? The only time BIT might make sense is if you were storing a yes/no attribute in a tremendously large table and were concerned about total table size.