Char and Varchar

My company proposes changing all char(x) columns to varchar(x) on the grounds that "Char stores data in a fixed length format and if data varies in length from row to row then space is wasted. By using varchar we can use the storage space more efficiently " What about fixed length fields? If we have a char(10) column, and we know that every item in that column will be exactly 10 characters long, then is there any advantage in making it a varchar? I think it might even be less efficient. I presume that for a varchar the database must store the length of the data, in addition to the data, so there would be extra resources used. Am I right? For a fixed length field, is char or varchar better? Or doesn't it make any difference?

Peter Morris wrote in message <8vtslb$b28$1@lyonesse.netcom.net.uk>... >My company proposes changing all char(x) columns to varchar(x) >on the grounds that <etc etc etc> Like all "all-or-nothing" proposals it is not a good idea to go the whole hog. **) First area to consider is within your programming. If you are using 4GL (or languages offering similar semantics for the chars and varchars) then: *) The automatic clipping of spaces from chars (in all but one circumstance) WILL DISAPPEAR and that can have serious consequences requiring reprogramming. You will need to ensure appropriate clipping is inserted in your application code. *) The circumstance where plain old chars are not automagically clipped is when used with the , (concatenate) operator but that's great because with chars it gives you a choice of glueing the strings together either with or without the intermediate blanks. You will have to review all use of the concatenate operator, because if you are not clipping, the assumed blanks included in the middle will all disappear. *) A very peculiar characteristic of varchars: if you clip a varchar that is full of blanks, it will yield a string of ONE blank, not an empty string. So once again, clipping of chars converted to varchars will need reviewing. This characteristic is very annoying with varchars, and is a consequence of the implementation of them in the 4GL. It will be troublesome if potentially empty strings are used with concatenation. *) Most paradoxical of all, varchars are NOT completely variable length, when compared with chars. Chars are implicitly variable length because the trailing blanks are ignored at all times (except with concatenate) so plain old chars in fact program exactly like variable length strings. IF you get trailing blanks on a varchar then 4GL and SQL will NOT remove it for you, and you can get peculiar results (=bugs) until you throw in a lot of clipping at suspicious points - that means examining your code and doing the appropriate thing at critical plpaces. Data entry etc can yield "invisible" spaces - you won't see them in selects or printouts, so you can waste time wondering why the code isn't working. Plain old chars are basically "more" variable than varchars in programming. *) If a programmer is relying on the fixed width of a char in printouts and displays, then first thing to say is "naughty programmer!" because that makes the program less flexible in the face of database changes. But seriously, if you change to varchars then reports and displays may well "wander" without appropriate use of the column control etc. **) Engine considerations: *) Max length of a varchar is 255 *) Total space consumed for a varchar(size, reserved) can be size + 1 *) Minimum space consumed for a varchar(size, reserved) can be reserved + 1. If you attempt to store less than reserved, then the space consumed in the rows is still reserved + 1, although the true length of the data will be correctly saved. "reserved" is merely a performance setting for updates. To exploit it, analyse your data and guess the most useful mimimum length so that most data will fit without reallocation to an overflow area if it grows. If rows are inserted but (the varchars are) never updated then you can safely set reserved to 1 because overflowing is only an issue during updates. *) VARCHARS in an index consume their length + 1 because indexes cannot have variable length "records" in them. *) The bit map associated with the table is larger when the rows are variable length, because it needs to store 4 bit entries in the bitmap instead of the usual 2 bit entries. *) Jack Parker mentions that varchars cannot participate in "light scans". If that doesn't mean anything to you, this is it: IF you have an index on columns A, B ... AND you issue a select which only selects some of the columns in the index AND the nature of the query means the engine can use the index to get those results (perhaps because of suitable joining or filtering, or no better reason NOT to use a light scan) THEN the engine will merely get the "rows" from the index without having to look into the table data itself. Hence the scan is "lighter". **) Other considerations: *) If a particular column is basically a code, then it will typically be fixed length or close to that, so it's not worth converting. *) If a column is informational (eg a street address) then it may be variable in length but possibly involved in SQL or 4GL activity that would be more easily programmed with fixed length chars. *) If a column is "narrational" ie plain old descriptions and chatter, then it's probably the most interesting candidate for conversion. These sorts of columns are less likely to be involved in any interesting programming, and are merely recorded, printed or thrown up on screens, so you will have less hassle converting. One problem with this type of column is that it's the most likely to exceed the max length of 255 available with varchars, and if you have a lot of those then you need to consider the consistency of your database and programming. *) Actual space saved would really depend on your data. You could make a program to examine all tables in a live database and make a prediction on the difference. You may be saddened by the less than expected results. **) Summary: There are issues with converting, so don't jump in with a dream of an easy conversion! Life may be simpler if you START a particular table with appropriate varchars right from the beginning, and you can use the considerations above to choose appropriate columns and deal with the programming consequences. Converting to varchars later will require conscientious and thorough examination of your codebase and also the performance considerations in the engine. You should be aware of the sad truth that there is no such thing as a "small change" in an established codebase.