Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Unanswered: Space usage question

    If I add a VARCHAR(128) column that is nullable to a table, does the database reserve those bytes for every row in the table?

    For example, lets say I have a table of USERS with two columns,
    USERID INT NOT NULL (the primary key) and
    USERNAME VARCHAR(16) NOT NULL
    to which I want to add an optional comments column
    COMMENTS VARCHAR(128).

    Would it be more effecient to add a new table called USERCOMMENTS
    with two columns,
    USERID INT NOT NULL (a foriegn key)
    COMMENTS VARCHAR(128) NOT NULL.

    There is more than disk space at risk here so I am not sure what is the
    best schema design to use.

    Thanks in advance,
    Wayne

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Wayne,
    I think the way you want to go is by adding the column to the existing table.
    My reasons: (assuming UDB LUW)

    1) it will take up less space. Adding the column will increase each row by 3 bytes plus the size of each comment when updated. 1 byte for null indicator, 2 for the length of the varchar. By creating a separate table each row will have 4 bytes for the int FK, 2 bytes for the varchar length and then the varchar. Plus any additional space for an index you will probably need on the USERID column.

    2) Why split up a 1:1 relationship.

    3) you will incur the cost of a (left outer) join to access the data in the comment table.

    HTH

    Andy

  3. #3
    Join Date
    Aug 2003
    Posts
    58
    Andy,
    Thanks for the info. I was afraid specifying VARCHAR(128) somehow
    reserved 128 bytes per row. Since this is not the case, clearly the
    better option is to use the one table. What is the purpose of the
    128 if a column is specified as VARCHAR? Why not specify every
    VARCHAR as 1024, just in case the data needs to be a bit bigger
    than originally planned?

    Wayne

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Wayne,
    When you define a varchar, the number specified is the maximum length the string can take. Any updates or inserts that contain strings longer than the maximum will be truncated on the right so that the size of the persisted string is the maixmum length. The length has other considerations also.
    1) indexes based on varchar are affected based on the length
    2) the pagesize a table must reside is based on the maximum varchar size. If you have large varchars, just to be safe, then you will need tablespaces with pagesizes big enough to hold the maximum size the row can be.

    My rule of thumb for varchar length is to initailly make them as small as necessary, remember their size can be increased if needed.

    HTH

    Andy

Posting Permissions

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