asked
Char vs Varchar
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.
Unless the column is a code that is always the same length,say 2-4 characters, you should almost always use varchar. For a varchar, say varchar(1000) the optimizer will estimate the data size to be half, or 500 bytes. If on average most of your data fills up more than 50% of the varchar size the optimizer will incorrectly estimate the size of data and may not allocate enough memory and spill sorts etc to the temp db. In this case you can increase the size of the varchar declaration to take advantage of in-memory sorts, if you have enough RAM.
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.
I've used casting to char as an easy way to add the padding when I needed to create a fixed-width flat-file before.
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.
