Inconsistent/Missing Information on Bit Special Datatype

Hello all,
I did some research trying to find an answer but was unsuccesessful.

Here's my question, does the bit special datatype support a null value in SQL 2000/2003?

I found an article on SQL 7.0 dated Mar 2001 by Sergey Vartanyan that states:

"Bit datatype is usually used for true/false or yes/no types of data, because it holds either 1 or 0. All integer values other than 1 or 0 are always interpreted as 1. One bit column stores in 1 byte, but multiple bit types in a table can be collected into bytes. Bit columns cannot be NULL and cannot have indexes on them."

On the other hand, when I look out at Microsoft's MSDN site I find the following in regards to SQL 2000:

"Consists of either a 1 or a 0. Use the bit data type when representing TRUE or FALSE, or YES or NO."

There is also this reference to bit for Transact SQL:
"Transact-SQL Reference
bit
Integer data type 1, 0, or NULL."

My personal opinion is if you require a 'yes/no' field, you wouldn't want to allow NULLs.

My reason for asking is I'm migrating from Access to SQL and within the Access tables some of the fields are YES/NO datatype but have Null values in some of the records.

One last thing, I know I can set it to a default of 0 or 1, but since I didn't write the application, I don't want to second guess the programmer. If newer versions of SQL will support NULL on the bit datatype, then it makes things easier for me.

Thanks in advance for any and all help.
T. MullinsThat is a $64,000 dollar question.

BOL states:

"Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on."

So it would appear that a bit value can take up as little as 1 bit of space. But bit values can clearly store NULLs:
--------
declare @.BitTest bit
Print @.BitTest

set @.BitTest = 0
select @.BitTest

set @.BitTest = null
select @.BitTest
---------
So how is it possible to store three possible states (1, 0, Null) in a single computer bit?

Obviously something else is going on behind the scenes, but I've never seen an explanation for it either.

blindman|||Originally posted by blindman
That is a $64,000 dollar question.

BOL states:

"Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on."

So it would appear that a bit value can take up as little as 1 bit of space. But bit values can clearly store NULLs:
--------
declare @.BitTest bit
Print @.BitTest

set @.BitTest = 0
select @.BitTest

set @.BitTest = null
select @.BitTest
---------
So how is it possible to store three possible states (1, 0, Null) in a single computer bit?

Obviously something else is going on behind the scenes, but I've never seen an explanation for it either.

blindman

Hey Blindman,
Thanks for the input. I guess I'll fall back on providing a default value if no data is passed in.

In(@variable) clause and TABLE datatype variable

Hi this question has been asked several times and some solution has been
provided already. But the one I am facing is with a twist. I need to use the
IN() clause with a variable as its parameter. The variable is a list of comm
a
separated character values all enclosed in pairs of single quotes. I could
have solved this problem by enclosing the final query in a single quote and
running Exec command on it (with the Variable list outside the quotes) but I
also need to use a Table data type variable which raises error when EXEC
command is run.
Followig is the example that may explain well.
I have oversimplified this example and it does things that we would not do
in normal situation
use pubs;
-- declare and set Table variable
declare @.TableVariable TABLE ( col char(4) );
INSERT @.TableVariable
Select pub_id FROM publishers
;
--declare and set CSV single quoted characters list
declare @.ListVariable varchar(100);
set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
publishers table, promise
--the query where the Table variable is used as well as the IN() clause is
used
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN (@.ListVariable)
-- returns 0 rows
--if we use Exec by replacing the last code section above with as following
declare @.command varchar(2000)
set @.command='
Select TableVariable.col
From @.TableVariable as TableVariable
Where TableVariable.col IN ('+@.ListVariable+')
'
exec (@.command)
--Then we get the error message:
-- Must declare the variable '@.TableVariable'.
Expand AllCollapse All
Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletterhttp://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--
"Aamir Ghanchi" <AamirGhanchi@.discussions.microsoft.com> wrote in message
news:D82370C5-6E5E-4178-98B8-0017FE88E809@.microsoft.com...
> Hi this question has been asked several times and some solution has been
> provided already. But the one I am facing is with a twist. I need to use
> the
> IN() clause with a variable as its parameter. The variable is a list of
> comma
> separated character values all enclosed in pairs of single quotes. I could
> have solved this problem by enclosing the final query in a single quote
> and
> running Exec command on it (with the Variable list outside the quotes) but
> I
> also need to use a Table data type variable which raises error when EXEC
> command is run.
> Followig is the example that may explain well.
> I have oversimplified this example and it does things that we would not do
> in normal situation
> use pubs;
> -- declare and set Table variable
> declare @.TableVariable TABLE ( col char(4) );
> INSERT @.TableVariable
> Select pub_id FROM publishers
> ;
> --declare and set CSV single quoted characters list
> declare @.ListVariable varchar(100);
> set @.ListVariable = ' ''0736'', ''0877'' '; --these pub_ids are in the
> publishers table, promise
> --the query where the Table variable is used as well as the IN() clause is
> used
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN (@.ListVariable)
> -- returns 0 rows
> --if we use Exec by replacing the last code section above with as
> following
> declare @.command varchar(2000)
> set @.command='
> Select TableVariable.col
> From @.TableVariable as TableVariable
> Where TableVariable.col IN ('+@.ListVariable+')
> '
> exec (@.command)
> --Then we get the error message:
> -- Must declare the variable '@.TableVariable'.
>
> Expand AllCollapse All
>
> Manage Your Profile |Legal |Contact Us |MSDN Flash Newsletter
>