Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2004
    Posts
    127

    do column nulls consume space?

    Hi, this may be seem a basic question but do columns that contain null consume any actual space at a physical level?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, none
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    127
    hi r937, thanks for quick reply!

    so for example if i have a table, with say 60 columns - all of which were functionally dependent on the PK & that no transitive dependencies existed either.

    if for every insert, i only populated five columns per record entry - this would not have an effect on storage space at all even though 55 columns per entry were null?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    The exact answer depends on your specific DBMS. I believe you use Oracle? In Oracle, trailing null columns consume no space, but non-trailling null columns consume 1 byte each. So if your table has columns (col1, ..., col50) and you do these inserts:

    insert into t (col1) values ('x');
    insert into t (col50) values ('x');

    then the first will just consume N bytes (whatever that may be), but the seconds will consume N + 49 bytes - 1 byte each for col1,...,col49.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by FAC51
    this would not have an effect on storage space at all even though 55 columns per entry were null?
    yes, that's right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tony: this is database concepts and design

    in concept, nulls take no space because they aren't there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    tony: this is database concepts and design

    in concept, nulls take no space because they aren't there
    It's not the nulls that are occupying space, it is the columns! My piggy bank is sadly empty, but it still consume space on its shelf

  8. #8
    Join Date
    Jun 2004
    Posts
    127
    hi tony, thanks for the reply.

    yes i am using Oracle 9.2.

    i also think i get what your saying. Hence under Oracle, columns containing nulls that are 'sandwiched' by columns containing values, will incur a 1 byte value per null column? as these are non-trailling null columns?

    is this in the Oracle documentation?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    It's not the nulls that are occupying space, it is the columns! My piggy bank is sadly empty, but it still consume space on its shelf
    i keep my coins in my pocket, and i assure you, when it's empty, it's empty! what, suddenly my pants have no pocket when it's empty?

    but i don't have slots in my pocket for each coin that isn't there, so i don't know why oracle needs them

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by FAC51
    i also think i get what your saying. Hence under Oracle, columns containing nulls that are 'sandwiched' by columns containing values, will incur a 1 byte value per null column? as these are non-trailling null columns?

    is this in the Oracle documentation?
    Correct. Yes, it is in the documentation e.g. in the Concepts Guide.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    From a purely logical perspective, Rudy has a case. Most of us live in the physical world, so we need to allow for its non-theoretical (like that phrase?) limitations. Most database engines need something to show that a column is NULL, therefore most engines do use some space somehow to store the representation of a NULL value.

    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my subtle way of suggesting that a question about physical space taken by a NULL should be in the forum for that specific database, not in a forum for concepts

    after all it could be as little as a single bit

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2004
    Posts
    127
    Tony, when you design tables, do you design to ensure that columns which will often be null are specified as trailing null columns? or is this something that is quite often considered a trivial factor to you?

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by FAC51
    Tony, when you design tables, do you design to ensure that columns which will often be null are specified as trailing null columns? or is this something that is quite often considered a trivial factor to you?
    I would put the not-null columns first when creating an Oracle table, so as not to use up space unnecessarily. But I'm quite happy to add further not-null columns later at the end if needed: I wouldn't immediately feel the need to re-build the table unless it was a really big and super-critical table.

  15. #15
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Tony is right.

    Nulls cause (in the physical world) more than just a space expense. The treatment required to (a) store and (b) process them is expensive (variable length fields are slower than fixed length). In the Sybase world, it is well established that databases intended for high performance, do not allow Nulls, due to the expense. There are several ways around this (to allow for the "Undefined" value or row) in a physical HP database, all of which conceptually allow the "Undefined" occurence but not the physical implementation of it.

    But in a General, design, sense, it is good practice to avoid nulls, just because they lead to outer joins, incomplete resolution of the normalisation process, etc. The point is the occurence of the Null value has to be handled by the code, in any case. The utopian goal therefore is to not have Nulls, and thus allow for code that does not have to be sensitive to Nulls.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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