Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557

    Question Unanswered: Char vs varchar data type

    I was asked by the developer to create a table and one of the columns on this table they are asking to be a varchar(25). For some unknow reason to me I decided to dig dipper into it.

    Some information states that is the column is defined as varchar and if there are regular updates and new data for the column is bigger then the original, the record will be moved to another page in the table.And then it will cause significant performance issue since multiple pages are required to return a single row.

    Other


    • If the size of actual data varies significantly, VARCHAR is more efficient because CHAR adds extra blank characters to fill the field. These blank characters must be transmitted across the network like any other characters.
    • If the size of actual data does not vary much, CHAR is more efficient because each VARCHAR field has a few bytes of length information which must be transmitted.
    It seams to me that since they are requesting only 25 bytes, and if they are going to be doing regular updates it would be beneficial to define this column as a char even so it will hold extra blank characters, but it wont have many single record pages.

    Anyone willing to offer a different take on it? Thank you.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You are correct that if DB2 cannot fit the row in the same space as before, and there is no more room on the page, then it will have to move the row to another page with a pointer from the original location. So if the varchar length is likely to be increased, the char might be better if the update volume is high.

    If you cannot use char in the above situation, leave plenty of percent free on the table, and reorg often.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Thank you sir.

    I am in the strugle trying to get more info from developer right now.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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