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

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

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

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