On 8/15/06, Rick Schummer <[EMAIL PROTECTED]> wrote:
Varchar is a very good thing when used for columns that vary drastically, or have data occasionally needing more space than common for the column. If I have data like postal code I know is always 5 or 6 characters I would make this Char. If I have a comment field where the user normally puts in 50 or so characters, but occasionally might get verbose with 100, then I might make the column Varchar(125) just to leave the user with some extra room for growth. Varchar(1) makes no sense to me, but there may be something I have missed or overlooked in my database 101 class.
You're exactly right. A field that always has five or nine characters makes little sense as a varchar. In theory, varchar requires more overhead. But, if any field in a record has a varying length, the implementation of data storage might mean all fields have varied start and end locations within the data record. (Perhaps the designers stuff all the fixed-column records at the front and the varchars at the end, perhaps they just use one technique for all fields even though some don't vary in size.) In some back-end-storage engines for MySQL, if any field is varchar, all the character fields are made varchar. I think this is a bit pushy: make them variable-length storage on disk, but don't annoy the database designer <s>. If the data engine stores nullable values as a separate bitmap as its done in VFP, a varchar(1) could actually make a bit of sense: a million rows where one value is 1 and two values are 2 and the rest are NULL would take up two million bytes as a fixed length field (one for the character, one for NULL bitmap) but only one-million-three bytes as a varchar (assuming the same storage of start, end and length for both methods). But I doubt it's a situation a lot of data storage engine designers optimize for <s>. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
