How are bit column stored?

  • I have been looking for an article which explains how bit columns are stored. I seem to remember that they share bytes, with 8 bit columns per byte.  Is that true? If I alter a table to add a bit column will it first try to share, then allocate a byte?

    I searched MSDN and BOL and came up dry!!!!!

     

    Michael

  • quote from BOL:

    Index-->Bit data type-->described

    Remarks

    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.



    Michelle

  • SQL will pack bit fields together and allocate another byte only on the 1st, 9th, 17th, ... bit field.

    If your bit field is nullable it requires another bit in the null bitmap (like any other nullable field).  So if your 9th bit field is also the 9th (or 17th, etc) nullable field the row size may increase by two bytes.

    Look at the "Estimating the size of a database" topics in BOL for more details.  To be more precise, the three "Estimating the size of a table" topics under it in the contents.

  • I just posted these links yesterday in response to another post - there're a couple of excellent articles on bitwise operators/operations by the "database geek" featured on yesterday's front page...

    get wise about bits

    more bitwise wisdom







    **ASCII stupid question, get a stupid ANSI !!!**

  • And another "bit" of useful information:

    If you place an index on a bit column, the optimizer will not properly choose that index if you use a literal '0' or '1' in a predicate expression.  These have to be explicitly cast to bit, or the optimizer thinks they're integers.  Odd, but true.

    So use ...where [bit column] = convert(bit,0)... instead of ...where [bit column] = 0...

    jg

     

     

     

  • Just out of interest, how do you get an index on a bit column? BOL states "Columns of type bit cannot have indexes on them."

  • You can create an index on a bit column using the normal CREATE INDEX syntax.  EM won't let you select a bit in an index.

    Also you can use QueryAnalyzer's GUI.  Type in SELECT * from [Table Name] and generate an execution plan.  Then right-click the Table or Clustered Index Scan icon, and select "Manage Indexes" from the menu.

    From there you can select the columns you'd like in a new or existing index, including any bits.

    Keep in mind that placing an index on a bit column is usually not very helpful.  In some circumstances, however, it can be very helpful.

    hth

    jg

     

     

     

  • You may have an out-of-date BOL.  It is now legal to use bit fields in indexes.

  • Ah, I didnt know that. I generally use SQL to create indexes, but since I initially started using EM I only tend to do what it allows, hence not knowing that about indexing bit columns. Seems to be a slight error in BOL then.

    Just out of further interest, could you give an example of the occasions when it could be very helpful... im going to start testing it now with one particular execution plan that looks as though it might appreciate it, but would like to know a little more..

  • One reason not to use bit fields in indexes is that they are not very selective, with only two values (three if you count NULL).

    If you have a frequently-run query that requires a few fields (including the bit field), and it causes a table scan because the fields are not part of the clustered key, creating a secondary index with the bit field and other fields required by the query will give you a covering index and improve the performance of that query.

  • Briefly...

    There are two cases that come to mind.  One of them would involve adding a bit to the tail of an index if the value was needed to cover a query, or perhaps to satisfy an IF EXISTS query.  This is fairly straightforward.

    Another situation would be if the bit signifies something by which you would identify a relatively small subset of a population.  So, it would not be useful to index the bit if it would signify the predicate "Is the person Male?" (Where 1 = yes, Male and 0 = no, female) in the general population.  The reason that it would not be useful in this case is that it would likely be more expensive to generate a list of bookmarks with which to seek than it would be to just scan an entire table.

    Now, if the same predicate were to apply not to the general population, but instead to a population where a particular value would stand out, then it could be useful because the list of bookmarks would be short, and thus less expensive to generate and use than a full scan.  If this logic were applied to a table containing the population of the Men's Basketball team, then the Female members would certainly be rare. 

    Perhaps a more useful example would be to mark records that you'd like to highlight for a particular process.  Perhaps "Deletion at Midnight" would be such a process.

    Usually, that could be more efficiently handled by populating another table with the keys from the target table, and joining them for the process, but sometimes, the DBA must work within an existing design.

    I hope your experiment works out.  Let us know...  Just don't forget to cast your literal to a bit.

    jg

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply