SQL Server 2008 bit field best design practices

  • The analysis is interesting, however, can make life difficult for developers that use the database.

  • Craig Farrell (6/14/2011)


    Some Bit vs. Bitmap testing.

    That was unexpected. Thanks for posting this.

  • lfernandesfotos (6/14/2011)


    The analysis is interesting, however, can make life difficult for developers that use the database.

    It depends on the developers 😀

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • allmhuran (6/14/2011)


    Craig Farrell (6/14/2011)


    Some Bit vs. Bitmap testing.

    That was unexpected. Thanks for posting this.

    My pleasure, I personally was a little shocked myself on the newer engines. I'd thought they'd cleaned this up better.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • lfernandesfotos (6/14/2011)


    The analysis is interesting, however, can make life difficult for developers that use the database.

    Developers should be going through procs anyway. I can hand you a four hundred parameter SProc that then turns a few hundred y/n fields into bitmasks without you ever knowing I did it. It also self-documents that way nicely.

    The times this becomes a problem is when the N-Tier would try to tightly couple to the database layer, instead of using Sprocs as their interface.

    As I mentioned earlier, fields that have the opportunity to expand in meaning (my laundry example) should be used as a TinyInt or better, since they're really status flags. Bits should really only be used for definitive Y/N attributes that can stand alone, and masks for very specific high-speed processing of groups of similar attributes.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (6/15/2011)


    lfernandesfotos (6/14/2011)


    The analysis is interesting, however, can make life difficult for developers that use the database.

    Developers should be going through procs anyway. I can hand you a four hundred parameter SProc that then turns a few hundred y/n fields into bitmasks without you ever knowing I did it. It also self-documents that way nicely.

    In many places I've worked, it's the developers that write the procs.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (6/15/2011)


    Craig Farrell (6/15/2011)


    lfernandesfotos (6/14/2011)


    The analysis is interesting, however, can make life difficult for developers that use the database.

    Developers should be going through procs anyway. I can hand you a four hundred parameter SProc that then turns a few hundred y/n fields into bitmasks without you ever knowing I did it. It also self-documents that way nicely.

    In many places I've worked, it's the developers that write the procs.

    A number of the shops I've worked in have a dedicated DB Dev that handles 3-4 app teams. It's not so much the volume of work as knowing what you're doing when you're in there. However, at my current shop, I'm quite familiar with that sad fact. :crying:

    I guess it depends on what you mean by developer... and how much training they've had. Still, for an app developer's standpoint, they can code it that way too and forget the mask existed from the front end.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • What I mean is I have 6 years experience in databases and never see anyone use the bitmap datatype. The performance analysis is sensational, but is only usable if you work with databases or tables really giants.

    I do not know what everyone's opinion on the subject, but I usually work with BIT, CHAR, or tinyint to work with these cases mentioned (as the case of laundry).

    Not when I worked in companies that had databases very well designed I noticed the use of certain types of data. Even the use of common data types, as ROWGUID were very rare.

    If this happens, I imagine that a developer will be surprised by the code and do not see much advantage in complicating because we know a little more of theory.

  • lfernandesfotos (6/15/2011)


    What I mean is I have 6 years experience in databases and never see anyone use the bitmap datatype. The performance analysis is sensational, but is only usable if you work with databases or tables really giants.

    Well, it's actually just an INT, it's the usage that turns it into a BitMap, but I think I get what you mean. 🙂

    Not when I worked in companies that had databases very well designed I noticed the use of certain types of data. Even the use of common data types, as ROWGUID were very rare.

    UniqueIdentifier has its own optimization issues, the first being that INTs compare faster so they make poor surrogate keys.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 9 posts - 16 through 23 (of 23 total)

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