CHAR() vs VARCHAR() advice

[former_SQLA_member1694903]

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).

[Breck_Carter]

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.

[former_SQLA_member1694903]

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()

[Former Member]

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.

[Breck_Carter]

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

[VolkerBarth]

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.

[VolkerBarth]

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).