Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question Unanswered: Bit Field vs. Integer with Nulls

    If I have a field where 99.9% of the time the answer is going to be "No", would I be better, in terms of disk space, using:

    A bit field

    OR

    A tiny int field, with a NULL for the 99.9% that are "NO", and a 1 for those that are "YES".

    I'm using SQL Server 7.0.

    My application developer has no preference.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How much data are we talking about?

    Also What is the field for?

    Do you have to display the data?

    I'd go with....

    CREATE TABLE myTable99(Col1 char(3) DEFAULT('NO') CHECK(Col1 IN ('NO','YES')))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Data Size

    Roughly 8000 records a month. The field is used to identify those records that are going to be non-billable, which is a very small percentage of the time.

    The field itself will never appear on any report; it will determine if the particular record should appear on the standard report, or if it will be moved to a special report.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett dude! You'd seriously store "No" and "Yes" in the database?

    Excesive verbosity!

    "Y" or "N", or 1 or 0 thank you!

    I don't believe that a single bit field is going to take any less space than a smallint. I think you have to have several bit fields grouped in your table to realize any space savings.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yo,

    Blind dude

    You'd seriously prefer to perform conversions for display purposes for only 8k rows a month?

    SELECT CASE WHEN Col1 = 1 THEN 'YES' WHEN Col1 = 0 THEN 'NO' END

    This is like using a surrogate....no thanks

    So, you're saving 16k a month.....let's through a party....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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