Re: CHAR() v. VARCHAR()

Mitch, Your DBA's explanation leaves a bit to be desired. The difference between CHAR and VARCHAR is that CHAR takes up a fixed amount of space on the disk while VARCHAR takes up only the space required for the actual string (or the number of bytes specified by r if you use the VARCHAR(m,r) syntax). For example DEFINE char_str CHAR(10) DEFINE var_str1 VARCHAR(10) DEFINE var_str2 VARCHAR(10,5) LET char_str = "A" LET var_str1 = "A" LET var_str2 = "A" On disk, the following space will be used (I use the hyphen to mean a space) char_str A--------- var_str1 A var_str2 A---- (<- 5 bytes total because of its declaration) So, varchars make much better use of you disk space-wise. There is, of course, a price to pay. When you add varchars to a table, the rows are no longer of a fixed length. If your select does a sequential scan of the table, it will now take longer because it will need to calculate the starting location of each row. For example, before the engine can examine record #2, it must figure out the length of record #1. Etc. Also, record updates become more difficult. For example, imagine a record with a varchar of 10 bytes. If you update the record so that the varchar is now 20 bytes, the record will most likely have to be moved to another spot on the disk where it can gain the 10 extra bytes it needs. So, the pros and cons work out like this: Pro: Disk space is conserved when the lengths of data items vary widely or when only a few items are longer than average. (i.e. The r value of the VARCHAR(m,r) syntax) Pro: Queries on the more compact tables can be faster. Con: Updates of a table can be slower in some circumstances. Con: Sequential scans can be slower (as if they weren't bad enough already!) As far as some of the other comments go. 1) I think that char strings are stored with their space padding on the disk. It doesn't matter, though, since they are allocated a fixed number of bytes either way. 2) VARCHAR is not equivalent to TEXT in anyway! Blobs are handled completely differently than VARCHAR! 3) Varchar may or may not be efficient depending upon your circumstances and what you mean by efficient (disk space vs. performance). My personal experience is that, for the most part, char is better than varchar. However, there definitely are times when a varchar is what you want. Good luck! If you have any specific questions, please feel free to e-mail me direct. --Craig (craig@chemistry.utah.edu) > > We are using Informix 7.01. We are using PowerBuilder for developing > client/server applications. PowerBuilder does not strip the padding off > of the CHAR columns when rows are added to a DataWindow. > > Our DBA claims that VARCHAR() is much less efficient than CHAR(). Yet, > he claims that both are fixed length columns with null terminated string > data. He claims that Informix pads the CHAR() columns either at the time > of the SELECT or at the time of transmission to the client. If both are > null terminated strings in storage and the CHAR() is padded when > retrieved, why would CHAR be more efficient for storage of string data. > It would seem that padding the data at the time of retrieval would make > CHAR() less efficient re. server resources than VARCHAR(). > > I can appreciate that most SQL servers store VARCHAR() data less > efficiently than CHAR() data but VARCHAR() has a very different meaning > on most SQL servers (i.e., equivalent to Informix TEXT with a maximum > length specified). > > We need to decide whether it is better to store our string information as > either CHAR() or VARCHAR() under Informix. >