Quick char/varchar question

[disruptivehair]

Hi...I'm not sure if this is true but I've heard some DBAs say that it's better to use char(1) instead of varchar(1) for a column that will only contain one letter and that is never null.

Just wondered if this is true.

[davide++]

Hi all.

First, Oracle recommends to use VARCHAR2 datatype instead of VARCHAR.
Then, CHAR(n) differs from VARCHAR2(n) because CHAR fields of lenght n are always filled for all n characters, eventually with blanks in the left side, whereas VARCHAR2 contain exactly the actual number of characters that they have to contain.
For example, if is

CHAR(10) NAME1
VARCHAR(10) NAME2
and both fields have to keep the string "DAVIDE", will be
NAME1 = 'DAVIDE '.
NAME2 = 'DAVIDE'.

If n = 1, there aren't differences of course, but if n > 1 using CHAR should be dangerous, because the text that field contains isn't what one awaits. Nevertheless, with CHAR Oracle doesn't have to manage the "variable part" of the text, so CHAR(1) is more efficent than VARCHAR2(1), (but if n > 1 using VARCHAR2 is recommended).

[hspc]

disruptivehair..You did not specify the database engine you use. the answer to your question depends on the RDBMS used.
davide++ told talked about Oracle. But considering MS SQL Server, the answer should be yes..Use char(1) instead of varchar(1). because in the case of varchar, SQL Server must store the actual occupied space by the field. this space is an integer value and takes 2 bytes as I recall. so using varchar(1) will cause SQL Server to use 2 or 3 bytes not only 1 which is a space loss. this also will cause extra processing.
Generally speaking.. using char,nchar vs varchar,nvarcahr depends on the maximum length (n).
You can take these values only as a guide :
n < 20 -> use char,nchar
n > 40 -> use varchar,nvarchar

MySQL has a special handling to char fields. according to some rules, char columns may be converted to varchar (called silent conversion).

[cjard]

Hi...I'm not sure if this is true but I've heard some DBAs say that it's better to use char(1) instead of varchar(1) for a column that will only contain one letter and that is never null.

Just wondered if this is true.

Probably - anything you can do to remove the variance in length of a record is generally considered to be an improvement in performance. If you had 1000 rows each 1000 bytes long you could get row 999 by seeking to byte 999000 on disk. If the records varied in length, you would have a harder job of finding your row. Thats an overly simple example but it illustrates the point