Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2009
    Posts
    12

    Performance question about using NULL in a column.

    Say I have a table called "person" with a column called "age" where age can be an integer or a NULL value.

    My question is what's the RAM and performance difference between having a column in a table that has a NULL value, for say 50% of the rows, or having a separate table (of that column) with the primary key from the other table (create a table called "age" with the "person" primary key in it, but having no NULL values in it)?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Performance: It will be quicker with the NULL value because you don't need to join with another table to get the age.

    Space: A NULL value takes up one byte (depending on system) to store so you'd have to add this to the storage requirements. If you're using a separate table then you'd only be storing the values you know and not the Nulls however you'd also need to store the id of each user alongside each age in the new table. RAM is a different type of memory to what the database is stored in.

    Complexity: Your code will be more complex if you use a separate table.

    Each user should have an age unless it's unknown so it should be stored as a simple field in the user table and allow NULL. You could always force the user to input a birth date and get round the issue

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends what you are doing - there are lots of different operations, some of which would perform better and some worse depending on what you do. Most practitioners would just have a NULLable column, relational purists are more likely to go for the one to one relationship.

    BTW - even more important is you don't ever store age, not unless you are keen on keeping a team of staff employed 365 days a year incrementing 1/365th of the table's values every day.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2009
    Posts
    12
    Cool thanks.
    Sorry the Age column was just a poor example that I chose. I don't actually have a column for age.

    One more question though. Say I had like 5-10 columns in one table that could have a lot of NULL values. Is there a difference then?

    Let me be more detailed. I have a table that has about 2-3 columns that are "NOT NULL" and about 8-10 columns that could possibly be "NULL" based on if the user wishes to fill out that information. This table will often (almost always) be JOINED with a few other tables. Currently I'm developing the table with just "NULL" columns but I'm thinking about just creating a new table for each so that in the long run (if I have to add to the table) I can just create a new table instead of adding a new column.
    Thoughts?

    Also, this table should eventually have a ton of rows and will be my largest table of the database. I'd say it could eventually have about 400-800 rows inserted daily.
    Last edited by ss1289; 02-04-09 at 13:45.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Keep your design simple, and keep all attributes of an entity in one table, regardless of whether those values might be null.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by ss1289
    One more question though. Say I had like 5-10 columns in one table that could have a lot of NULL values. Is there a difference then?

    Let me be more detailed. I have a table that has about 2-3 columns that are "NOT NULL" and about 8-10 columns that could possibly be "NULL" based on if the user wishes to fill out that information. This table will often (almost always) be JOINED with a few other tables. Currently I'm developing the table with just "NULL" columns but I'm thinking about just creating a new table for each so that in the long run (if I have to add to the table) I can just create a new table instead of adding a new column.
    Thoughts?
    Without knowing more about your application I'd say exactly the same thing as before. It's quite ordinary to have joins on a few tables but if your 8-10 columns are moved out into separate tables then your queries now not only have to join with your existing "few extra tables" but with another 8-10 extra tables as well. This will definitely be inefficient!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ever notice how many actual databases relational purists build?

    answer: they're too busy ranting about the joys of relational algebra on newsgroups and discussion forums

    i should know, i once made the mistake of admitting in public that i *heart* NULL

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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Code:
    CREATE TABLE DBAs
    	(DBAID int NOT NULL IDENTITY (1, 1),
    	DBAName varchar(50) NOT NULL,
    	IsOKWithNulls bit NULL) 
    GO
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bit NULL -- that's gotta be a classic!!!

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

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    DBA humor. Cracks 'em up every time.
    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
  •