Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83

    Unanswered: Bit datatypes physical storage

    Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).


    Is there any good documentation about the physical storage layout for a data page?

    Thanks -

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    From SQL BOL:
    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.
    A Null column does not consume any storage. SQL Server stores Nullable columns only if they exist. That's good unless they change frequently, then it can be bad.

    In general, avoid Nulls as much as you possibly can for two reasons:
    1. Data Integrity
    2. Reduced Page Splits
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i won't question point 2 because it seems to be another instance of DBA arcana (with all of which i am not familiar), but point 1 cannot go unchallenged

    are you suggesting 6th normal form? because i have a real problem with that

    and if not, then what are you suggesting? zeros instead of nulls in numeric columns, and empty strings in character columns? how does this improve data integrity? if anything, this decreases data integrity

    or were you specifically referring to null bit fields only? because that wasn't clear (and doesn't make a lot of sense either)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    Ok, if I have 2 bit fields which are then stored in the same byte, and the first field is null, there must be a way that MSSQL knows that without thinking that the first bit of that byte which stores the bit fields is the first bit column value. I'm thinking that there is an offset table on the row, or some kind of null "indicator". Which is it? An offset table or an indicator?

    If an offset table, then how are bit fields represented in it? If an indicator, then whoa, I'm lost on why I would choose such a datatype.

  5. #5
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Thanks for the challenges, r937. I'm not prone to making sense. My point is if a value is expected, then don't allow Nulls.

    How often do you see DDL posted and all columns but the primary key are Nullable (just be happy there's a primary key, right?). I see it too often.

    I like the aracana misconception - no secrets here as I'm a recovering pragmatist. However, I quesiton my ability to explain it succinctly and I'm burnt out on searching MSN for links that do the job for me.

    But, I'll try for posterity.

    Page Splits occur when SQL Server has to reorganize data on a page and Page Splits are one of the most expensive operations that SQL Server has to do. Updating a value from Null to something that Is Not Null may cause a Page Split on a page that is > 50% full (I'll leave the research up to you for determing the conditions under which SQL Server splits a page).

    For a heavy OLTP system, it gets expensive. Thus, if you force users to enter values that are needed, you reduce the probability they'll have to go back and add the values later, thus reducing the probability of a Page Split.
    Finally, my working mode is enterprise applications. So, I answer questions with the assumption the answer should apply to an enterprise architecture. I'm often amused how my assumptions lead to useless typing, but hey, I'll keep trying.

    Which is it? An offset table or an indicator?
    Offset. Which led Microsoft to disallow Null bits in the beginning. They succumbed to pressure. Your seeking internals knowledge, and Microsoft has been relaxing their rules around providing it, but I'm not sure they've gone into any depth regarding offsets. I'd tell you more but I'd have to shoot you.

    Hope this helps. I gotta Split.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MaxA
    My point is if a value is expected, then don't allow Nulls.
    well, sure, if one knows what one's doing (although i would change the word "expected" to "required")

    but that's a lot different than "avoid Nulls as much as you possibly can"

    the latter advice will cause some people to use DEFAULT 0 and DEFAULT '' needlessly, wantonly, and, in most cases, flat out incorrectly

    as far as all columns but PK being nullable, i myself like that idea

    it allows the referential integrity action "ON INSERT CASCADE [to parent]" (yes, i made that up) whereby if you try to insert a child row with a foreign key for a parent that doesn't exist yet, it takes the foreign key value and uses it to insert a row with that value of the primary key in the parent table (so all other parent columns are therefore assigned NULL or get their defaults) before inserting the child row

    would save writing a trigger

    and don't say you haven't seen an application where this is a valid business rule
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    I was gonna leave this alone, but then I'm a pedant.

    The world in which I live requires parents to exist for a child to exist. Now, that could just be my world and with the advent of cloning, it may quickly be invalidated and I'll have to move to a different world.

    If you do the math on your interesting CASCADE [to parent] you will find that the parent insert should require knowing all the parent's attributes to do the insert. My interpretation of your proposition is that parent attributes are irrelevant. They're relevant in my world.

    Where ya gonna put 'em and how ya gonna get them if you just ignored them?

    I have not seen a valid business for which this type of an application would rule.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Avoid nulls as much as you can?

    Bullcrap! The value NULL provides information that simply cannot be provided by a default value, that being that absence of information. If a value is unknown, then the last thing I would want would be to have a value place their by default.

    I'd classify that as bad data in a heartbeat.

    Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."

    There is nothing wrong with a table schema that allows many columns to be null. Are you going to prevent a user from entering a record with incomplete information just to satisfy the obsesive compulsions of an overly anal-retentive dba?

    Ultimately, the business rules drive the design. If they do not require a value, then allow NULL.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."
    Who's being anal-retentive?

    Expecting values implies they are required. Saying as much as you can implies you are competent enough to know when you can't. I'll switch to explicit mode to avoid illogical conclusions.

    I didn't say completely avoid Nulls, I said avoid Nulls as much as you can.

    I agree - if a value is not required, then allow Null. But, avoid Nulls as much as you can.

    I didn't say ignore them. I didn't say they don't mean anything even though their meaning is unknown. I said avoid Nulls as much as you can.

    I didn't say mislead others with default values. I said avoid Nulls as much as you can.

    If a value is expected, then don't allow Nulls. That is reality.

    That's my story and I'm sticking to it.

    Avoid Nulls as much as you can.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Man, I love this. MaxA, - you rule!!!!

    But looking back at my own designs, - I can tell thatt the better I understood the business requirements, the more complex but at the same time cleaner the final design looked like. And ironicly I did notice that fewer nulls were present. Offloading NULLable columns to a separate table always leads to more complex queries, but a LEFT OUTER JOIN is a LEFT OUTER JOIN, and if PKs/FKs are properly indexed, - you'll see a better performance as well...But that's just me

    EDITED: By better performance I mean that your table will naturally be theoretically wider when nullable columns are present, and at the time of optimizing your SELECT you have 2 choices: avoid selecting nullable columns, or include them into existing indexes to avoid bookmark lookups. But if you offload those columns into a separate table that will only contain records where the column is not null, it'll result in fewer records to go through and all indexes are used.
    Last edited by rdjabarov; 02-16-05 at 10:33.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Avoid NULLS as much as you can? That is just a tautology. If you are saying not to allow NULLS when a value is required, then I completely agree with you.

    How about this statement: "Avoid making a field required as much as you can."

    I didn't say completely avoid Non-Nulls, I said avoid Non-Nulls as much as you can.

    I agree - if a value is required, then disallow Null. But, avoid Non-Nulls as much as you can.

    I didn't say ignore Non-Nulls. I didn't say they should be used even when a value is unknown. I said avoid Non-Nulls as much as you can.

    If a value is not required, then allow Non-Nulls. That is reality.

    That's my story and I'm sticking to it.

    Avoid Non-Nulls as much as you can.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh that's just plagiarism, Mr. Lindman, come up with your own style!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Plagiarism IS a style. Just look at Hollywood or the Grammy Awards!

    "Plagiarism is the sincerest form of flattery".
    I forget who originally said that....
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Apparently Charles Caleb Colton:

    http://www.quotationspage.com/quote/27484.html

    I just don't think he has ever gotten enough credit for his work, do you?
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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