x

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
more ▼

asked Dec 12, 2011 at 03:05 AM in Default

aRookieBIdev gravatar image

aRookieBIdev
2.3k 53 57 62

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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.
more ▼

answered Dec 12, 2011 at 03:25 AM

sql_follower gravatar image

sql_follower
107

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.
Dec 12, 2011 at 03:36 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Dec 12, 2011 at 03:29 PM

Tarwn gravatar image

Tarwn
72 1

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Dec 12, 2011 at 03:34 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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.
Dec 12, 2011 at 11:08 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x244
x108
x37

asked: Dec 12, 2011 at 03:05 AM

Seen: 1859 times

Last Updated: Dec 12, 2011 at 03:05 AM