Skip navigation

Should I use char or varchar? What are the advantages?

A. There's no "right" answer to the above as it depends on your data and the access profile.

If your field is fixed length or is almost fixed - i.e. it is either 8 or 9 characters then stick to char.

If the field is heavily updated and this causes the row-size to change then you're probably better off with varchar.

If your data is VERY variable in length or may often be blank then the advantage from using varchar is that smaller overall rowlengths give more rows per page and therefore faster performance when retrieving/scanning multiple consecutive rows. 

Many people still think that updating varchars causes a deferred update rather than in-place and for this reason don't advise their use for updateable columns. This has not been true since SQL 4.x. With 6.0 and above as long as the updated column will still fit on the same page then the update is done directly and therefore the performance overhead is not massive. Note that there are different sorts of direct updates listed below in order of speed.

a) Direct update. Fixed columns only, less than half of row bits changed, 5 columns max.

b) Update-in-Place. Fixed or varchar, before and after row must be the same size, less that half the row bits changed.

c) On-page delete/insert. Any number of changes, but row must fit on same page.

If these don't apply then a full delete/insert must occur.


TAGS: SQL
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish