Is SQL CHAR data type obsolete? When do you use it?

Question

The title asks a lot of the question. I have not used CHAR for years. Right now, I am processing a database containing CHARs around the world for primary keys, codes, etc. What about the CHAR column (30)?

Edit: So, the general consensus seems to be that CHAR, if it works great for certain things. However, I think that you can create a database schema that does not need “these certain things”, which does not require fixed-length strings. With bit, uniqueidentifier, varchar and text types, it seems that in a well-normalized scheme, you get a certain elegance that you don’t get when you use encoded string values. Thinking in fixed lengths, no crime seems to mean a relic of mainframes (I myself recognized RPG II). I believe that this is outdated, and I have not heard convincing arguments from you, arguing otherwise.

Answer 1

If the nature of the data dictates the length of the field, I use CHAR. Otherwise VARCHAR.

Answer 2

I use char (n) for codes, varchar (m) for description. char (n) seems to lead to better performance because the data does not need to be moved when the content is resized.

Answer 3

CHARs are processed even faster than VARCHARs in DBMSs, which I know well. Their fixed size allows you to optimize, which is not possible with VARCHAR. In addition, the storage requirements are slightly less for CHARS, since the length should not be preserved, assuming that most of the rows should completely or almost completely fill the CHAR column.

This has less effect (in percent) on CHAR (30) than on CHAR (4).

Regarding use, I prefer to use CHAR if:

• fields will usually always be close or maximum (stock codes, employee identifiers, etc.); or
• lengths are short (less than 10).

Anywhere I use VARCHAR.

Answer 4

I use CHAR when the length of the value is fixed. For example, we generate code or something based on some algorithm that returns code with a certain fixed length, for example 13.

Otherwise, I found VARCHAR better. Another reason for using VARCHAR is that when you get the value back in your application, you do not need to trim that value. In the case of CHAR, you get the full length of the column, whether the value will be filled in completely or not. It will be filled with spaces, and you will finish trimming each value and forget what will lead to errors.

Answer 5

For PostgreSQL, the documentation states that char() has no storage advantage over varchar() ; the only difference is that it is filled with a space up to the specified length.

Having said that, I still use char(1) or char(3) for single-character or three-character codes. I think that clarity due to the type indicating what the column should contain provides value, even if there are no storage or performance benefits. And yes, I usually use validation restrictions or foreign key restrictions. Besides these cases, I usually stick with text instead of using varchar() . Again, this is reported as a result of a database implementation that automatically switches from built-in to external storage if the value is large enough, which some other database implementations do not.

Answer 6

Char is not outdated, it should be used only if the field length should never change. In an average database, this would be very few fields, basically some kind of code field, such as state abbreviations, which are the standard 2 character provided if you use postal codes. Using Char, where the string length is constant, means that there will be a lot of trimming, and this is an extra, unnecessary work, and the database needs to be reorganized.