SQL Server Performance

Bit vs Int field

Discussion in 'SQL Server 2005 General Developer Questions' started by jagblue, Jul 10, 2008.

  1. jagblue New Member

    Hi AllI have a unique situationWe have a table wilt around 4 million+ records and growing 100k + per monthAnd we are planning to add around 26 new bit columnsHow space and performance will affect in these scenario.To have 26 new flag fields
    Or One Int field and then UI to convert INT in to flag
  2. moh_hassan20 New Member

    SQL Server optimize storage of bit columns and every 8 bit fields are stored as one byte

    The 26 bit field are stored as 4 bytes (mooore save) [;)]
    You can create 26 bit field , and no need for UI to convert INT into flag
    Columns of type bit cannot have indexes on them, and store 0 /1 /null only.

  3. FrankKalis Moderator

    [quote user="moh_hassan20"]
    Columns of type bit cannot have indexes on them, and store 0 /1 /null only.
    [/quote]
    Sorry, but this is not entirely correct. I guess you're thinking of SQL Server 2000 and Enterprise Manager. In 2000 you could only index such a column via script. The GUI simply just don't allow you to do this. I just checked again and in 2005 you can also create a index via SSMS.
    The question remains if a separate index on such columns make sense. [:)]
  4. satya Moderator

    It basically depends on how important that index is for your queries. There is a considerable space penalty for building an index on a bit column (there is a key or RID stored for every row). But if the index is important, go ahead and build it.
  5. FrankKalis Moderator

    [quote user="satya"]
    It basically depends on how important that index is for your queries. There is a considerable space penalty for building an index on a bit column (there is a key or RID stored for every row). But if the index is important, go ahead and build it.
    [/quote]
    I would rather add such columns as included columns or regular columns to the index to create a covering index instead of creating a "stand-alone" index. [:)]
  6. satya Moderator

    I agree but when it comes to the space issues (which we normally see users asking why my database size is big) it is better to suggest what documentation refers, but as a whole this point is upto the DBA who is managing the application database to decide whether its good to have index or not [:)]./
  7. Adriaan New Member

    I wonder if a bitmask search on an integer column will profit from an index on that column. If so, it is probably a better option than having a set of indexed bit columns.
  8. moh_hassan20 New Member

    [quote user="FrankKalis"]
    [quote user="moh_hassan20"]
    Columns of type bit cannot have indexes on them, and store 0 /1 /null only.
    [/quote]
    Sorry, but this is not entirely correct. I guess you're thinking of SQL Server 2000 and Enterprise Manager. In 2000 you could only index such a column via script. The GUI simply just don't allow you to do this. I just checked again and in 2005 you can also create a index via SSMS.
    [/quote]
    Thanks Frank for that information which is new for me. [:D]
    [quote user="FrankKalis"]The question remains if a separate index on such columns make sense[/quote]
    I usually prefer using bit fields as boolean T/F , Y/N .
    Based on the index design guidelines, i think that there no need for index in such fields.

Share This Page