Hi everyone, I am currently working on designing a database tables, which i am not really used to.can anyone pls let me know the difference between char and varchar , when would should one use them and what are their pros and cons?? Kannan
Char is a fixed length character data type and the storage size of the char value is equal to the maximum size of the Column. Varchar is a variable-length character data type and the storage size of the varchar value should be the actual length of the data entered and not the maximum size for this column. There will be some performance using Char but practically you would never see unless you have a high transaction application.
In addition to what @sql_follower says, remember that padding is always added to data added to a CHAR field in order to ensure that it meets the data length. So, for a CHAR(5) if you store the value 'Dog' it actually stores 'Dog '. The padding translates automatically as spaces. Other than that, they're both strings and behave in roughly similar fashion.
A quick addition to Grant and sql_follower (I don't have ability to comment yet, sorry): A varchar will actually consume N+2 bytes, where N is the size of the data you are storing in the field and the 2 bytes represents the value of that size. A char is always a constant storage size (and padded, as others have pointed out). So from a storage standpoint if your data is always 9 or 10 characters long (for instance), then a char(10) would be 10 bytes per record, but a varchar(10) would be 11 or 12, costing 1-2bytes/record over the char(10). Typically the way I'll check for this is to put a sample set of data in a varchar field and select an average, like so: SELECT MAX(SampleField) - AVG(LEN(SampleField)) FROM SampleTable; If the value is less than 2 then I know the data is consistent enough that char will give me a space savings and this savings will apply not just to the table but also any additional indexes the field is included in.