Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    37

    Talking Unanswered: 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. Mullins

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  3. #3
    Join Date
    Oct 2003
    Posts
    37

    Smile

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •