question

aRookieBIdev avatar image
aRookieBIdev 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
databasedatabase-designdatatypes
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sql_follower avatar image
sql_follower answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Tarwn avatar image
Tarwn answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.