Ask the Expert

Booleans in SQL Server

I want to query a SQL Server database with my VB6 program, using an SQL statement. I want to make a selection on a boolean (WHERE myBoolean = TRUE), but this statement always gives me a syntax error. I guess I need to add some kind of brackets or something in order to show SQL it's a boolean value, like ' ' for strings, { } for dates, ... Can you help me out? Been looking all over the Web for an answer.

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

    Requires Free Membership to View

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.


This was first published in October 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: