I know this topic has probably been hashed to death, but I can't find any guidance to hang my hat on.

Over the Thanksgiving weekend, I will be doing a dbUnload/dbLoad of our ERP database, which is about 30GB in size. One of the tables (and at 60 million records, by far the largest), has two columns that I need to make bigger. The columns are currently defined as CHAR(30). I believe that CHAR(60) will be big enough for the foreseeable future. The question, of course, is should they be changed to VARCHAR(60)? These fields are populated in about 80% of the records, and are both indexed (not unique).

asked 08 Nov '12, 07:54

BudDurland's gravatar image

BudDurland
3169920
accept rate: 0%


As far as the database is concerned, CHAR and VARCHAR are the same datatype: variable-length character strings; e.g., CHAR ( 30 ) and VARCHAR ( 30 ) can both contain 'xyz' with no padding, just the three characters.

As far as SOME CLIENT-SIDE INTERFACES are concerned, CHAR may be interpreted as a fixed-length character string. If such a client-side interface pads a value with blanks to fill the maximum length when doing an INSERT or UPDATE, SQL Anywhere will store those blanks. This behavior is pretty rare and getting rarer, and it probably does not affect you... and it is all performed outside SQL Anywhere.

IMO there's no downside to declaring the column CHAR ( 100 ) or CHAR ( 200 )... certainly not from SQL Anywhere's point of view.

permanent link

answered 08 Nov '12, 08:31

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

I learn something new everyday. I always thought that CHAR() was stored in the database as space-padded. The trade off was that CHAR() offered a slight performance advantage, since the db engine didn't have to retrieve/compute the actual string length as in VARCHAR()

(08 Nov '12, 08:54) BudDurland
Replies hidden
2

Some systems and the SQL standard differentiate between CHAR and VARCHAR but as far as the server goes there is little if any distinction between the two types. You can set an option with ODBC connections whether you want blank-padding on fixed-length CHAR types or not.

There are some algorithms in the server where the maximum data type size does impact execution strategies - for example, subquery caching is disabled when the subquery involves CLOB or BLOB values. Breck is correct that declaring your string field to be CHAR(100) or VARCHAR(200) isn't going to cause any significant behaviour or performance changes.

(08 Nov '12, 10:28) Glenn Paulley
Replies hidden
Comment Text Removed

"You can set an option with ODBC connections whether you want blank-padding on fixed-length CHAR types or not."... what is that option?

(08 Nov '12, 11:14) Breck Carter

AFAIK, ASE and MS SQL Server (at least older versions) treat CHAR and VARCHAR as different as you seem to expect, so with them, this is an important design decision.

(08 Nov '12, 12:47) Volker Barth

It's the odbc_distinguish_char_and_varchar option, methinks - and it decides how CHAR is described (as SQL_CHAR or SQL_VARCHAR - the latter being the default).

(08 Nov '12, 13:01) Volker Barth
Your answer
toggle preview

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

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275
×30

question asked: 08 Nov '12, 07:54

question was seen: 4,097 times

last updated: 08 Nov '12, 13:02