Character Data

Character data consists of any combination of letters, symbols, and numbers. When entered, character data must be enclosed with single quotation marks ('). If numbers are stored as character data, they must be converted to a numeric datatype (decimal, float, int, money, numeric, real, smallint, smallmoney, or tinyint) before you can use them in calculations. If the value of the string is the null value, do not enclose NULL in single quotation marks ('). To supply the null value, use the keyword NULL during INSERT or UPDATE.

Note An empty string is not considered the same as NULL. An empty string with no characters, supplied as ' ', will insert a single blank space.

These are the character datatypes:

char[(n)]

Is a datatype that holds a maximum of 255 characters. Specify the maximum length with n. The char datatype can contain 0 characters, but when specified, n must be a value from 1 through 255. Storage size is n regardless of the actual length of the entry.

Choose char when you think the data entries in the column will be consistently close to the same size. Columns of type char are accessed somewhat faster than varchar columns because they use a fixed storage length (n).

Note When the char datatype is used with the ALTER TABLE or CREATE TABLE statement, a column defined as NULL will be handled as a varchar column.

varchar[(n)]

Is a datatype that holds a maximum of 255 characters. Specify the maximum length with n. The varchar datatype can contain 0 characters, but n must be a value from 1 through 255. Storage size is not fixed with a varchar column. Instead, storage is defined by the actual size of the data entered (ignoring all trailing blanks).

Choose varchar when you expect null values or a wide variation in data size.

Important When n is not specified in a data definition statement (such as CREATE TABLE) or in a variable declaration, the length supplied is 1 (the ANSI-standard). When n is not specified with the CONVERT function, SQL Server determines the appropriate value of n based on the length of the data being converted.

If the data entered is too long for the specified datatype, char and varchar entries are truncated. Similarly, if you enter strings that are shorter than the maximum length, char columns that do not allow null values and char variables will be padded with trailing blanks; char columns that allow null values and varchar columns or variables will not store trailing blanks. This is important when using the LIKE comparison operator with varchar and char strings. Other comparison operators, such as the equal sign (=), always right-pad the shorter string with blanks before comparing. For more information, see the Search Conditions and Wildcard Characters topics.